Wrong Results or ORA-01877 Caused By Query With More Than 1004 Both Select Columns and Tables (Doc ID 2383660.1)

Last updated on APRIL 02, 2025

Applies to:

Symptoms

• 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,
  D.PID DML_PID,
  D.C_NO DML_C_NO,
  ... ...
  D01.DID D01_DID,
  D01.A_DATE D01_A_DATE,
  D01.VAL_ID D01_VAL_ID,
  ... ...
  D02.DID D02_DID,
  D02.A_DATE D02_A_DATE,
  D02.VAL_ID D02_VAL_ID,
  ... ...
  D03.DID D03_DID,
  D03.A_DATE D03_A_DATE,
  D03.VAL_ID D03_VAL_ID,
  ... ...
  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.

•  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.
• 
  
  select
  ... ..
         ,"VM_NWVW_2" . ROWID "PLAN_DATE"
  ... ..
      FROM
          (
              SELECT
  ... ...
                      ,"D01" . ROWID "ROWID"
                      ,"D02" . ROWID "ROWID"
                      ,"D03" . ROWID "ROWID"
  ....
          ) "VM_NWVW_2"
      ORDER BY
          "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.

Changes

 Upgrade from 11.2 to 12.1.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.