Re: dynamic sql statement failure

Thanks i knew it would be something relatively simple, thanks for the quick response

Will

baboehme_at_hotmail.com wrote:
> 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 - 11:44:17 CEST