Re: dynamic sql statement failure

[Quoted] Oracle evaluates the whole block before attempting to execute, but the string used in execute immediate is not validated until it runs. So, it looks at exeucte immediate 'anything' and considers that valid, then looks at the insert statement and cannot validate it, because the table to insert into does not exist yet. If you are going to create the table dynamically, then you must also insert into it dynamically, as demonstrated below.

scott_at_ORA92> begin
  2 execute immediate 'create table c (col1 number,col2 varchar2(30))';
  3 execute immediate 'insert into c values (123,''data'')';   4 end;
  5 /

PL/SQL procedure successfully completed.

scott_at_ORA92> desc c

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 COL1                                               NUMBER
 COL2                                               VARCHAR2(30)

scott_at_ORA92> select * from c
  2 /

      COL1 COL2

---------- ------------------------------
       123 data

scott_at_ORA92> Received on Fri Jul 29 2005 - 19:53:49 CEST