Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Dynamic sql failure
Thanks all for your useful comments
Have received an answer from another group fyi:
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 Sun Jul 31 2005 - 04:48:11 CDT