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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: