Interesting case of ORA-01792 error.
One of my customers had a very long but not complex SQL statement generated dynamically and hit the following ORA error.
ORA-01792: maximum number of columns in a table or view is 1000
You have very little chance to meet this error in the real life. The definition of the error code is like following.
01792 "maximum number of columns in a table or view is 1000" // *Cause: An attempt was made to create a table or view with more than 1000 // columns, or to add more columns to a table or view which pushes // it over the maximum allowable limit of 1000. Note that unused // columns in the table are counted toward the 1000 column limit. // *Action: If the error is a result of a CREATE command, then reduce the // number of columns in the command and resubmit. If the error is // a result of an ALTER TABLE command, then there are two options: // 1) If the table contained unused columns, remove them by executing // ALTER TABLE DROP UNUSED COLUMNS before adding new columns; // 2) Reduce the number of columns in the command and resubmit.
But the customer did not have any table whose column count is over 1000. Besides that the SQL statement was just a plain query not any type of DDL.
A close look on the definition would tell you that a view also has a 1000 limitation. So I requested the source SQL statement and the statement has following pattern.
select * from ( select b, decode(a, 1, 1) as c1, decode(a, 2, 2) as c2, ... decode(a, 1000, 1000) as c1000 from t1 )
- The SQL statement has declared unnecessary inline view which seems to be generated automatically.
- The inline view has declared just over 1000 dynamic columns using decode.
So, it seems that Oracle cannot declare columns more than 1000 inside a inline view. Let me prove it with following simple test case.
ORA-01792 error with the inline view containing 1001 columns.
UKJA@ukja1021> declare 2 v_cursor sys_refcursor; 3 v_sql varchar2(32767); 4 begin 5 v_sql := 'select * from (select '; 6 for idx in 1 .. 1000 loop 7 v_sql := v_sql || rpad('1',10, '1') || ' as c'|| idx || ', '; 8 end loop; 9 v_sql := v_sql || '1 from dual)'; 10 11 open v_cursor for v_sql; 12 close v_cursor; 13 end; 14 / declare * ERROR at line 1: ORA-01792: maximum number of columns in a table or view is 1000 ORA-06512: at line 11
Without inline view, no ORA-01792 error.
Elapsed: 00:00:00.06 UKJA@ukja1021> UKJA@ukja1021> declare 2 v_cursor sys_refcursor; 3 v_sql varchar2(32767); 4 begin 5 v_sql := 'select '; 6 for idx in 1 .. 1000 loop 7 v_sql := v_sql || rpad('1',10, '1') || ' as c'|| idx || ', '; 8 end loop; 9 v_sql := v_sql || '1 from dual'; 10 11 open v_cursor for v_sql; 12 close v_cursor; 13 end; 14 / PL/SQL procedure successfully completed.
One thing you should keep in mind is that Oracle limits the 1000 columns while it is checking syntax. Removing view with view merging would have no effect on this 1000 limitation.
This customer had the luck to be free from ORA-01792 error simply by removing unnecessary inline view. But under more complex situation, the workaround would have not been that easy.