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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Dynamic sql failure

Re: Dynamic sql failure

From: <william_hulse_at_hotmail.com>
Date: 31 Jul 2005 02:48:11 -0700
Message-ID: <1122803291.450184.279000@g14g2000cwa.googlegroups.com>


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

Original text of this message

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