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';