ORA-1792 in Oracle DB 12c

Today a customer came up with a problem that occured after going from Oracle Database Version 11.2 to 12.1. A query against a view returned the ORA-1792 error indicating that there are more than 1000 columns. But that was simply not true. So when investigating this, we found that the classic syntax works fine but the ANSI join syntax raises this error.
Just follow this simple test case that creates three tables with 501 columns each which are then joined. And please don’t blame me for being vulnerable to SQL injection attacks…

SQL> declare
  2    i number;
  3    j number;
  4  begin
  5    for i in 1..3 loop
  6      execute immediate 'create table t' || trim(to_char(i)) || '(id number)';
  7      for j in 1..500 loop
  8        execute immediate 'alter table t' || trim(to_char(i)) || ' add (s' || trim(to_char(j)) || ' number)';
  9  end loop;
 10    end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> select count(*) from user_tab_columns where table_name like 'T_';

  COUNT(*)
----------
      1503

WARTA @ orge>
WARTA @ orge> select t3.*
  2  from t1, t2, t3
  3  where t1.id = t2.id
  4    and t2.id = t3.id;

no rows selected

SQL> select t3.*
  2  from t1
  3  join t2 on (t1.id = t2.id)
  4  join t3 on (t2.id = t3.id);
join t3 on (t2.id = t3.id)
     *
ERROR at line 4:
ORA-01792: maximum number of columns in a table or view is 1000

A quick research in My Oracle Support revealed this document: Select Statement Throws ORA-01792 Error in 12c (Doc ID 1951689.1).

So the simple workaround is to disable fix control for bug 17376322.

SQL> alter session set "_fix_control"='17376322:OFF';

Session altered.

SQL> select t3.*
  2  from t1
  3  join t2 on (t1.id = t2.id)
  4  join t3 on (t2.id = t3.id);

no rows selected

And voila, the query now works without throwing any errors anymore. Problem solved.

Advertisements