Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Interesting case of ORA-01792 error.

with one comment

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.

About these ads

Written by Dion Cho

September 10, 2009 at 4:25 am

Posted in Troubleshooting

Tagged with

One Response

Subscribe to comments with RSS.

  1. Good one.

    It’s really rare to hit this ORA error but a 1000 column select statement… isn’t it too much.

    Asif Momen

    September 24, 2009 at 10:08 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 59 other followers

%d bloggers like this: