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>