Wrong Results or ORA-01877 Caused By Query With More Than 1004 Both Select Columns and Tables
Last updated on MAY 01, 2018
Applies to:Oracle Database - Standard Edition - Version 184.108.40.206 and later
Information in this document applies to any platform.
- Customer has a large query with 871 select columns including some Date columns, and 138 outer join tables, in which several tables was out join dozens of times:
select D.DID DML_DID,
from TBL1 D
LEFT OUTER JOIN TBL2 D01 ON (D.DID = D01.DID AND D1.VAL_ID = 148)
LEFT OUTER JOIN TBL2 D02 ON (D.DID = D02.DID AND D1.VAL_ID = 149)
LEFT OUTER JOIN TBL2 D03 ON (D.DID = D03.DID AND D1.VAL_ID = 150)
* Both columns and table names of above query have been replaced by simple ones.
- This query returns wrong value 11011.24225858989898977543 in Date column when executing by SQL Developer.
- This query fails with ORA-01877 when executing by SQL*Plus.
ORA-01877: string is too long for internal buffer
- From the final query after transformation in 10053 events trace file, the merged internal view "VM_NWVW_2" has more than 1000 column,and ROWID is listed as that Date column in final select list, which cause above problem.
,"VM_NWVW_2" . ROWID "PLAN_DATE"
,"D01" . ROWID "ROWID"
,"D02" . ROWID "ROWID"
,"D03" . ROWID "ROWID"
"VM_NWVW_2" . "$vm_col_868"
,"VM_NWVW_2" . "$vm_col_867"
,"VM_NWVW_2" . "$vm_col_866"
- This problem can only happen when query has more than 1005 field both Select Columns and Tables.
Upgrade from 11.2 to 12.1.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms