Monday, January 20, 2014

ORA-01792

I tested this example in Oracle 11.2. First I created a table with 1 column called COL1: 

SQL> create table tab1 (col1 number)
  2  /
 
Table created.
 
SQL>
 
Then I used a PL/SQL loop and EXECUTE IMMEDIATE to add COL2 through COL1000:
 
SQL> declare
  2    sql_statement varchar2(100);
  3  begin
  4  for i in 2..1000 loop
  5    sql_statement :=
  6    'alter table tab1 add (col'||i||' number)';
  7    execute immediate sql_statement;
  8  end loop;
  9  end;
 10  /
 
PL/SQL procedure successfully completed.
 
SQL>
 
I checked that TAB1 had 1000 columns:
 
SQL> select count(*) from user_tab_cols
  2  where table_name = 'TAB1'
  3  /
 
  COUNT(*)
----------
      1000
 
SQL>
 
Finally, I tried to add an extra column. This failed with an ORA-01792, proving that you cannot have more than 1000 columns in a table:
 
SQL> alter table tab1 add (col1001 number)
  2  /
alter table tab1 add (col1001 number)
                      *
ERROR at line 1:
ORA-01792: maximum number of columns in a table or
view is 1000
 
SQL>

No comments: