SQL select statement throws ORA-01792 error in Oracle 12.1.0.2.0 version.

ORA 1792: maximum number of columns in a table or view is <nnnnn>

Cause

The problem matches Unpublished bugs:

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

REDISCOVERY INFORMATION:

See 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.

Solution

The workaround is to set "_fix_control"='17376322:OFF'

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.