Insert within PLSQL fails

From: hrishy <hrishys_at_yahoo.co.uk>
Date: Fri, 29 Jun 2012 10:10:22 +0100 (BST)
Message-ID: <1340961022.46688.YahooMailNeo_at_web29010.mail.ird.yahoo.com>

Hi
I have the following piece of plsql.
Insert within the plsql 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;
/

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jun 29 2012 - 04:10:22 CDT