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?
Requires Free Membership to View
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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation