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 last published in July 2012

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Join the conversation

2 comments

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

instead of the current insert statement, execute immediate 'insert into row_chain_demo (col1,col256) values(1,''Hello World'')'
will work
Cancel
Also note that the commit before the insert is redundant. DDLs(create/alter table) implicitly issue commits.
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close