Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: dynamic sql statement failure

Re: dynamic sql statement failure

From: <william_hulse_at_hotmail.com>
Date: 31 Jul 2005 02:44:17 -0700
Message-ID: <1122803057.604059.247020@g43g2000cwa.googlegroups.com>


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 - 04:44:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US