Here's Connor again...
Note that the following code has been "anonymised" to protect the guilty.
procedure P is
begin
...
...
execute immediate 'drop table T';
execute immediate 'create table T as select * from ......';
...
...
...
for i in ( select * from T ) loop
...
...
end loop;
end;
Ah, a mix of dynamic and static references... Now how precisely did that compile? Nope, I'm not sure either.
4 comments:
SQL> CREATE TABLE X (A VARCHAR2(2),B NUMBER);
Table created.
SQL> INSERT INTO X VALUES ('A',1);
1 row created.
SQL> INSERT INTO X VALUES ('B',2);
1 row created.
SQL> INSERT INTO X VALUES ('C',3);
1 row created.
SQL> INSERT INTO X VALUES ('D',4);
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL>
SQL> CREATE TABLE T (A VARCHAR2(2),B NUMBER);
Table created.
SQL> --so T exists
SQL> CREATE OR REPLACE procedure P is
2 begin
3 execute immediate 'drop table T';
4 execute immediate 'create table T as select A,B from X';
5 for i in ( select * from T ) loop
6 DBMS_OUTPUT.PUT_LINE('THIS IS FROM T.A:'||I.A||' T.B:'||I.B);
7 end loop;
8 end;
9 /
Procedure created.
SQL> show errors
No errors.
SQL>
SQL> BEGIN
2 P;
3 END;
4 /
THIS IS FROM T.A:A T.B:1
THIS IS FROM T.A:B T.B:2
THIS IS FROM T.A:C T.B:3
THIS IS FROM T.A:D T.B:4
PL/SQL procedure successfully completed.
SQL>
SQL> DROP PROCEDURE p;
Procedure dropped.
SQL> DROP TABLE t;
Table dropped.
SQL>
SQL> --NOW T DOESN'T EXIST
SQL> CREATE OR REPLACE procedure P is
2 begin
3 execute immediate 'drop table T';
4 execute immediate 'create table T as select A,B from X';
5 for i in ( select * from T ) loop
6 DBMS_OUTPUT.PUT_LINE('THIS IS FROM T.A:'||I.A||' T.B:'||I.B);
7 end loop;
8 end;
9 /
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE P:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/13 PL/SQL: SQL Statement ignored
5/27 PL/SQL: ORA-00942: table or view does not exist
6/5 PL/SQL: Statement ignored
6/47 PLS-00364: loop index variable 'I' use is invalid
SQL>
SQL> drop table x;
Table dropped.
SQL> drop procedure p;
Procedure dropped.
SQL> exit
and probably just including the "S" of the "CTAS" in the for loop would sidestep the need for creating the X table, anyway...
I bet some interesting things would happen if you got a bunch of sessions using this at once ...
This is just another developer who doesn't understand Oracle's implementation of Global Temporary Tables. Very possibly they're converting MS TSQL into Oracle PL/SQL.
Cheers, APC
Post a Comment