|
Home > Archive > Other Oracle database topics > July 2005 > Dynamic sql failure
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Dynamic sql 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
| |
| Sybrand Bakker 2005-07-29, 1:23 pm |
| On 29 Jul 2005 10:20:53 -0700, william_hulse@hotmai
l.com wrote:
>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
1 has been discussed before
2 convert the code above into ordinary sql removing the begin end;
pair and you will be ok
3 It is generally considered bad programming to create tables on the
fly in pl/sql
--
Sybrand Bakker, Senior Oracle DBA
| |
| Jim Kennedy 2005-07-30, 3:23 am |
|
< william_hulse@hotmai
l.com> wrote in message
news:1122657653.531479.205590@g44g2000cwa.googlegroups.com...
> 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
>
Why are you creating a table? Also for insert it is considered better to do
insert into tablex(col1,col2,..) values (...);
Jim
| |
| william_hulse@hotmail.com 2005-07-31, 7:23 am |
| 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@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
------------------------------=AD----------- --------
----------------------------
COL1 NUMBER
COL2 VARCHAR2(30)
scott@ORA92> select * from c
2 /
COL1 COL2
---------- ------------------------------
123 data=20
scott@ORA92>
| |
| Sybrand Bakker 2005-07-31, 7:23 am |
| On 31 Jul 2005 02:48:11 -0700, william_hulse@hotmai
l.com wrote:
>table dynamically, then you must also insert into it dynamically, as
>demonstrated below.
Of course this a horrible approach as it will result in hard parses
all over the place.
The very reason Oracle invented pl/sql is to get rid of the hard
parses.
--
Sybrand Bakker, Senior Oracle DBA
|
|
|
|
|