ETL ODI Error |ORA-01792: MAXIMUM NUMBER OF COLUMNS IN A TABLE OR VIEW IS 1000

Are you seeing the error “ORA-01792: MAXIMUM NUMBER OF COLUMNS IN A TABLE OR VIEW IS 1000” when the original query does not select as many columns.

Cause
——–

The problem matches Unpublished bugs as per the Oracle documents:

Bug 19653859 – CI BACKPORT OF BUG 19509982 FOR INCLUSION IN DATABASE BP 12.1.0.2.2
Bug 19509982 – DISABLE FIX FOR RAISING ORA-1792 BY DEFAULT

Solutions:

  1. Oracle suggests to alter the query and/or view definitions to avoid the error.
  2. However in cases where the SQL cannot be adjusted then the checking can be disabled by:
    SQL> alter system set “_fix_control”=’17376322:OFF’;
  3. Alternatively, an interim patch(patch 19509982) can be applied to disable the error by default.

References:

•Bug 19509982 Disable raising of ORA-1792 by default

You can click here to read more

 

 

Examples:

SQL> alter session set “_fix_control”=’17376322:OFF’;

or at system level :

SQL> alter system set “_fix_control”=’17376322:OFF’;

OR

Apply Patch 19509982 if available for your DBVersion and Platform

To download the patch, please follow the steps below:
1) Go To MOS
2) Click on Patches & Updates
3) In the Patch Search Section, enter your patch number <19509982> in the Patch Name or Number field.
4) Click Search
5) Click the patch that meets your DbVersion and platform
6) Read any applicable notes before downloading, then click the download button.

 

Information courtesy : Google/Oracle Docs