9 Replies Latest reply on Jan 23, 2016 12:38 AM by user12152093

    Oracle 12c: ORA-01792 but not in Oracle 11g

    2913152

      I have a select statement that joins many tables with combined column count of over 1000.  I am selecting roughly 200 columns from various tables (far less than 1000 column limit).  In Oracle 11g, the select statement returns result but in Oracle 12c it throws error:

       

      ORA-01792: maximum number of columns in a table or view is 1000

       

      Note: Same result using either sql-developer or sqlPlus.

       

      It this a configuration issue or a bug in Oracle 12c?  Thanks for your time.

       

      SELECT * from v$version

       

      BANNER                                                                       CON_ID           

      -------------------------------------------------------------------------------- ----------------------

      Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0                
      PL/SQL Release 12.1.0.2.0 - Production                                       0                
      CORE    12.1.0.2.0    Production                                                     0                
      TNS for Linux: Version 12.1.0.2.0 - Production                               0                
      NLSRTL Version 12.1.0.2.0 - Production                                       0                

       

      5 rows selected

       

       

      Following script demonstrates the issue:

       

      declare v_sql varchar2(32767);

      begin

        -- create test table GREEN with 500 columns

        v_sql := 'create table GREEN (G_COLUMN_001 number(9) not null';

        for i in 1 .. 499 loop

          v_sql := v_sql || ',' || chr(10) || 'G_COLUMN_' || lpad(to_char(i+1), 3, '0') || ' varchar2(2) null';

        end loop;

        v_sql := v_sql || ')';

        execute immediate v_sql;

       

        -- create test table RED with 500 columns

        v_sql := 'create table RED (G_COLUMN_001 number(9) not null';

        for i in 1 .. 499 loop

          v_sql := v_sql || ',' || chr(10) || 'R_COLUMN__' || lpad(to_char(i+1), 3, '0') || ' varchar2(2) null';

        end loop;

        v_sql := v_sql || ')';

        execute immediate v_sql;

      end;

      /

      -- create test table PURPLE with 2 columns

      create table PURPLE (G_COLUMN_001 number(9) not null, P_COLUMN_002 varchar2(2));

       

      --********************************************************************************--

      -- selecting one column work!

      --********************************************************************************-- 

      select p.P_COLUMN_002

      from GREEN g

        join RED r on g.G_COLUMN_001 = r.G_COLUMN_001

        join PURPLE p on g.G_COLUMN_001 = p.G_COLUMN_001;

       

      --********************************************************************************--

      -- notice the purple table only has 2 columns, but selecting p.* fails in Oracle 12c:

      -- SQL Error: ORA-01792: maximum number of columns in a table or view is 1000

      --********************************************************************************--

      select p.*

      from GREEN g

        join RED r on g.G_COLUMN_001 = r.G_COLUMN_001

        join PURPLE p on g.G_COLUMN_001 = p.G_COLUMN_001;

       

      --********************************************************************************--

      -- sub selecting only one column also fails in Oracle 12c:

      -- SQL Error: ORA-01792: maximum number of columns in a table or view is 1000

      --********************************************************************************--

      select (select 1 from dual where 1 = 2) as exp_column

      from GREEN g

        join RED r on g.G_COLUMN_001 = r.G_COLUMN_001

        join PURPLE p on g.G_COLUMN_001 = p.G_COLUMN_001;

       

      drop table GREEN;

      drop table RED;

      drop table PURPLE;