Tools for Oracle database - dynamic sql statement failure

Author

dynamic sql statement failure

william_hulse@hotmail.com

2005-07-29, 1:23 pm

Hi all

The following:

begin
execute immediate 'create table c (col1 number,col2 varchar2(30))';
insert into c
values (123,'data');
end;

fails with:

ORA-06550: line 3, column 15:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 3, column 3:
PL/SQL: SQL Statement ignored

Even though commenting out the insert enables the table creation

Any ideas would be great

Cheers

Will

baboehme@hotmail.com

2005-07-29, 1:23 pm

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@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@ORA92> desc c
Name Null? Type
----------------------------------------- --------
----------------------------
COL1 NUMBER
COL2 VARCHAR2(30)

scott@ORA92> select * from c
2 /

COL1 COL2
---------- ------------------------------
123 data

scott@ORA92>

william_hulse@hotmail.com

2005-07-31, 7:23 am

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

Will

baboehme@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@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@ORA92> desc c
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> COL1 NUMBER
> COL2 VARCHAR2(30)
>
> scott@ORA92> select * from c
> 2 /
>
> COL1 COL2
> ---------- ------------------------------
> 123 data
>
> scott@ORA92>

Frank van Bortel

2005-07-31, 11:23 am

william_hulse@hotmai
l.com wrote:
[snip!]
I sincerely hope you code this type of stuff as:
- an exercise
- as part of an installation script
- as part of an upgrade script

But not, by any means, as part of production code, and
meant to be executed more than once, ever.

--
Regards,
Frank van Bortel

william_hulse@hotmail.com

2005-08-02, 7:23 am

Indeed, this is merely part of an installation script

It performs some initial checking of object existence so exceptions are
not raised when drop commands are issued

This is run as a 'once only' anonymous pl/sql block

Thanks for your help

Will

Sponsored Links