Q

Solve a PL-SQL error that creates dynamic tables

Oracle PL-SQL expert Daniel Clamage answers a question about a problem with doing an “insert” in PL-SQL to create a table dynamically.

More on Oracle PL-SQL

Read more expert answers from Dan Clamage

Ask your own question of our experts

I have the following piece of PL-SQL code. An “insert” within the PL-SQL fails. Any idea?

insert into row_chain_demo (col1,col256) values(1,'Hello World');
            *
ERROR at line 43:
ORA-06550: line 43, column 13:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 43, column 1:
PL/SQL: SQL Statement ignored

declare
l_create_table_query varchar2(2000) default 'create table row_chain_demo (col1 number) segment creation immediate' ; l_alter_table_query  varchar2(2000) ; l_column_name user_tab_columns.column_name%type default 'col' ;

begin
for x in (select *
            from dual
            where not exists (select null
                                from user_tables

                                where table_name = upper('row_chain_demo') ) ) loop
  execute immediate l_create_table_query; end loop;
dbms_output.put_line (l_create_table_query);
for l_cntr in 2..256
loop
  l_column_name       := 'col' || l_cntr ;
  l_alter_table_query := 'alter table row_chain_demo add  ' || l_column_name || ' char(2000) ' ;

  for x in (select *
                from dual
                where not exists (select null
                                    from user_tab_columns
                                    where table_name = upper('row_chain_demo')
                                    and column_name = upper( l_column_name )
                                 )
            )
  loop
    execute immediate l_alter_table_query ;
  end loop;
end loop;
commit;
insert into row_chain_demo (col1,col256) values(1,'Hello World');
end;
/

The table you’re creating dynamically doesn’t exist at compile time. Any static references to schema objects are validated at compile time. The static reference to your dynamic table in the final data manipulation language, or DML, statement must therefore also be done dynamically.

This was first published in July 2012

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

2 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close