forall ... execute immediate...

[Volker Hetzer]

Hi!
I've got a pls-00801 (internal error 74301) with running this testcase
through sqlplus:

------------------------------------------------------------
create or replace package perftest authid current_user
is
procedure testcase;
end;
/
create or replace package body perftest
is
procedure testcase
is
TYPE TablesToManageType IS TABLE OF user_tables.table_name%TYPE;
TablesToManage TablesToManageType;
begin
select table_name bulk collect into TablesToManage from user_tables where table_name like 'TYPEINS_%';
forall Tabno in TablesToManage.first .. TablesToManage.last
execute immediate 'drop table ' || TablesToManage(Tabno);
end;
end;
/
show errors;
exit
--------------------------------------------------
I know, the code is probably not optimal, but am I the only one getting this error?
My platform is redhat advanced server, oracle 9.2.0.3.0.

Lots of Greetings!
Volker
--
While it is a known fact that programmers
never make mistakes, it is still a good idea
to humor the users by checking for errors at
critical points in your program.
-Robert D. Schneider, "Optimizing INFORMIX
Applications"

[Volker Hetzer]

andrewst wrote: You CANNOT use FORALL with EXECUTE IMMEDIATE, only with INSERT, UPDATE or DELETE.
http://otn.oracle.com/sample_code/tech/pl_sql/htdocs/x/Bulk_Binding_Enhancements/Bulk_Binding_In_Native_SQL_In_Binding.htm

Lots of Greetings!
Volker
--
While it is a known fact that programmers
never make mistakes, it is still a good idea
to humor the users by checking for errors at
critical points in your program.
-Robert D. Schneider, "Optimizing INFORMIX
Applications"

[padderz]

Volker you are correct, FORALL with EXECUTE IMMEDIATE is supported as of
9i, however DDL (e.g. DROP TABLE) and dynamic PL/SQL statements are not
supported.

--
Posted via http://dbforums.com

[andrewst]

Originally posted by padderz Volker you are correct, FORALL with EXECUTE IMMEDIATE is supported as of 9i, however DDL (e.g. DROP TABLE) and dynamic PL/SQL statements are not supported.
Apologies for my misinformation. However, the 9.2. version of the
PL/SQL reference doesn't say this is possible, as far as I can see:

http://technet.oracle.com/docs/products/oracle9i/doc_library/release2/a-
ppdev.920/a96624/13_elems22.htm#34327

Needs updating, presumably.

--
Posted via http://dbforums.com

[padderz]

Tony, this is documented under EXECUTE IMMEDIATE, rather than under
FORALL. Mentioning it in both places would have been too thorough :-)

See...

]http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a9662-
4/11_dynam.htm#18131[/url]

--
Posted via http://dbforums.com

[Volker Hetzer]

padderz wrote: Tony, this is documented under EXECUTE IMMEDIATE, rather than under FORALL. Mentioning it in both places would have been too thorough :-) See... ]http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a9662- 4/11_dynam.htm#18131[/url]
Yes, I got that already from the doc cd.
By the way, I've shrunk the testcase a bit:
set serveroutput on
create or replace procedure testcase
is
TYPE TablesToEmptyType IS TABLE OF user_tables.table_name%TYPE;
TablesToEmpty TablesToEmptyType;
begin
select table_name bulk collect into TablesToEmpty from user_tables;
forall Tabno in TablesToEmpty.first .. TablesToEmpty.last
execute immediate 'delete from ' ||TablesToEmpty(Tabno);
end;
/
show errors;
/
REM execute testcase
/
show errors;
exit

Also, it doesn't use DDL anymore, it just tries
to "delete from" the tables that are there.
Btw, what is the supposed behavior if TablesToEmpty is empty?
Do I have to check for this or are first/last then null and forall does
nothing anyway?
Lots of Greetings!
Volker
--
While it is a known fact that programmers
never make mistakes, it is still a good idea
to humor the users by checking for errors at
critical points in your program.
-Robert D. Schneider, "Optimizing INFORMIX
Applications"

[padderz]

OK, well FORALL syntax is used for bulk binding of bind variables - as
in take this single SQL and execute it n times using this list of bind
variables. Concatenation of array elements to this SQL does not fit
with this model because you would typically no longer have a single
SQL statement but rather a different statement per iteration.
Presumably this COULD be supported but the savings would be far less
dramatic because of the high parsing overhead required to parse each
distinct SQL.

Recommend you pass up FORALL in this case for a simple FOR .. LOOP
through the array, where EXECUTE IMMEDIATE with a concatenated table
name will work dandy with DROP or DELETE.

--
Posted via http://dbforums.com

[Volker Hetzer]

padderz wrote: Recommend you pass up FORALL in this case for a simple FOR .. LOOP through the array, where EXECUTE IMMEDIATE with a concatenated table name will work dandy with DROP or DELETE.
Ok, will do. :-(
Lots of Greetings and thanks!
Volker
--
While it is a known fact that programmers
never make mistakes, it is still a good idea
to humor the users by checking for errors at
critical points in your program.
-Robert D. Schneider, "Optimizing INFORMIX
Applications"