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

一顧客社で多すぎる数のコラムをフェッチする長い(しかし複雑じゃない）SQL文章を動的に生成する際こんなエラーに当たりました。

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

このエラーに遭うことは多くないんです。エラーの定義を見れば次のようです。

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.

問題はどんなテーブルも1000個以上のコラムを持っていないということです。では、問題は何でしょうか。

エラーの定義をよく見ればテーブルだけでなくビューも1000個以上のコラムを持つことができないということが分かります。顧客社に問い合わせてSQL文をお願いしました。SQL文はこんなパタンでした。

select * from (
   select b, 
       decode(a, 1, 1) as c1,
       decode(a, 2, 2) as c2,
       ...
       decode(a, 1000, 1000) as c1000
   from t1
) 

• SQL文を動的に生成する過程で(多分不必要な）インラインビューを宣言しています。
  
• インラインビューは絶妙に1000個をかすかに超える数のコラムを動的に宣言します。
  

簡単なテストを通じてこれを証明してみます。

次のようにインラインビュー内で1000個以上のコラムを動的に宣言する場合にはORA－01792エラーが発生します。

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

しかしインラインビューを除いた場合には正常的に遂行されます。

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.

一つの問題は1000個の制限を判断する段階がOptimizationの段階ではなくてParseの段階、すなわちSyntax確認段階ということです。従ってMERGEヒントなどを通じてインラインビューを無理に除いてみてもORA-01792エラーを避けることができません。

幸いにこの場合には不必要なインラインビューを除くのだけで簡単に解決できましたけど、そうではなかったらかなり頭の痛い問題となったかもしれません。