Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

WHERE ROWNUM <= 65 issue with php

user10127912Aug 20 2014 — edited Aug 26 2014

Hi,

We have a Oracle database server 11.2.0.1 that run in Oracle Linux version 3.8.13-16.2.3.el6uek.x86_64, and a Linux 32-bit client server that run in Linux CentOS 6.2.

From the 32-bit client server if we run the query below by sqlplus we have no problem. When we run the same query from the same client server but by php page, it does not return more than 65 row. But (WHERE ROWNUM <= 60) it returns the result fine. Please help.

Aqlplus connection:

sqlplus appsodbc/password@palnerptest2.domain.net:1521/pterpdb02.domain.net

Sqlplus query:

SELECT * FROM ( SELECT p.T$NAMA AS NAMA, o.T$ORNO AS ORNO, CASE CAST(o.T$CORG AS INT ) WHEN 1 THEN 'Contracts' WHEN 2 THEN 'Quotations' WHEN 3 THEN 'EDI' WHEN 4 THEN 'Manual' WHEN 5 THEN 'Phone' WHEN 6 THEN 'Fax' WHEN 7 THEN 'Mail' WHEN 8 THEN 'Opportunity' WHEN 9 THEN 'CRM' WHEN 10 THEN 'Consumption' WHEN 14 THEN 'Order Template' WHEN 21 THEN 'Service' WHEN 22 THEN 'Intercompany EDI' WHEN 25 THEN 'Retro-Billing' WHEN 30 THEN 'Planning' WHEN 35 THEN 'Purchase' WHEN 40 THEN 'Shipment' ELSE 'Unknown' END AS ORD_ORIGIN, CASE CAST(o.T$HDST AS INT ) WHEN 2 THEN 'Suspended' WHEN 5 THEN 'Free' WHEN 10 THEN 'Approved' WHEN 20 THEN 'In Process' WHEN 25 THEN 'Modified' WHEN 30 THEN 'Closed' WHEN 35 THEN 'Cancelled' WHEN 40 THEN 'Blocked' WHEN 45 THEN 'Released' WHEN 50 THEN 'Not Applicable' ELSE 'Unknown' END AS ORD_STATUS, o.T$ODAT AS ODAT, o.T$CORN AS CORN, SUM((l.T$OAMT)) AS ORD_TOT FROM INFOR.TTDSLS400104 o, INFOR.TTDSLS401104 l, INFOR.TTCCOM100104 p WHERE o.T$ORNO = l.T$ORNO AND p.T$BPID (+)= o.T$STBP AND o.T$OFBP = '210008000' AND o.T$ODAT BETWEEN to_date('07/14/2014', 'MM-DD-YYYY') and to_date('08/14/2014', 'MM-DD-YYYY') GROUP BY p.T$NAMA, o.T$ORNO, o.T$ODAT, o.T$CORN, o.T$STBP, o.T$CORG, o.T$HDST ORDER BY o.T$ODAT DESC ) WHERE ROWNUM <= 500

odbc.ini :

[PTERPDB02]

Application Attributes=T

Attributes=W

BatchAutocommitMode=IfAllSuccessful

CloseCursor=T

DisableDPM=F

DisableMTS=T

Driver=Oracle_ODBC_Driver_in_dmz3

DSN=palnerptest2

EXECSchemaOpt=

EXECSyntax=T

Failover=T

FailoverDelay=10

FailoverRetryCount=10

FetchBufferSize=64000

ForceWCHAR=F

Lobs=T

Longs=T

MetadataIdDefault=F

QueryTimeout=T

ResultSets=T

ServerName=PTERPDB02

SQLGetData extensions=F

Translation DLL=

Translation Option=0

UserID=appsodbc

Php query:

'SELECT * FROM (SELECT

        p.T$NAMA AS NAMA,

        o.T$ORNO AS ORNO,

        CASE CAST(o.T$CORG AS INT )

                WHEN 1 THEN \'Contracts\'

                WHEN 2 THEN \'Quotations\'

                WHEN 3 THEN \'EDI\'

                WHEN 4 THEN \'Manual\'

                WHEN 5 THEN \'Phone\'

                WHEN 6 THEN \'Fax\'

                WHEN 7 THEN \'Mail\'

                WHEN 8 THEN \'Opportunity\'

                WHEN 9 THEN \'CRM\'

                WHEN 10 THEN \'Consumption\'

                WHEN 14 THEN \'Order Template\'

                WHEN 21 THEN \'Service\'

                WHEN 22 THEN \'Intercompany EDI\'

                WHEN 25 THEN \'Retro-Billing\'

                WHEN 30 THEN \'Planning\'

                WHEN 35 THEN \'Purchase\'

                WHEN 40 THEN \'Shipment\'

                ELSE \'Unknown\'

                END AS ORD_ORIGIN,

        CASE CAST(o.T$HDST AS INT )

                WHEN 2 THEN \'Suspended\'

                WHEN 5 THEN \'Free\'

                WHEN 10 THEN \'Approved\'

                WHEN 20 THEN \'In Process\'

                WHEN 25 THEN \'Modified\'

                WHEN 30 THEN \'Closed\'

                WHEN 35 THEN \'Cancelled\'

                WHEN 40 THEN \'Blocked\'

                WHEN 45 THEN \'Released\'

                WHEN 50 THEN \'Not Applicable\'

                ELSE \'Unknown\'

                END AS ORD_STATUS,

        o.T$ODAT AS ODAT,

        o.T$CORN AS CORN,

        SUM((l.T$OAMT)) AS ORD_TOT

FROM

        INFOR.TTDSLS400104 o,

        INFOR.TTDSLS401104 l,

        INFOR.TTCCOM100104 p

WHERE

        o.T$ORNO = l.T$ORNO AND

        p.T$BPID (+)= o.T$STBP AND

        o.T$OFBP = \'210008000\' AND

        o.T$ODAT BETWEEN to_date(\'07/15/2014\', \'MM-DD-YYYY\') and to_date(\'08/15/2014\', \'MM-DD-YYYY\')

GROUP BY

        p.T$NAMA,

        o.T$ORNO,

        o.T$ODAT,

        o.T$CORN,

        o.T$STBP,

        o.T$CORG,

        o.T$HDST

ORDER BY

        o.T$ODAT DESC ) WHERE ROWNUM <= 65';

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 23 2014
Added on Aug 20 2014
5 comments
2,134 views