Thursday 8 September 2011

Did you know that you can use FORALL to execute a dynamic PLSQL block

Question:
Steven, check this out:
The addition of EXECUTE IMMEDIATE as an option for FORALL actually gives us loads more options: you can do a nested FORALL as show below, for example. Note that this will only work if the inner collection is a SQL type, and it makes sense if the outer array is small and the inner arrays are large because we will parse (or soft parse) one PL/SQL block for every element of the outer array.
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE TYPE number_table_type AS TABLE OF NUMBER (10);
2 /

Type created.

SQL> CREATE TYPE array_type AS TABLE OF number_table_type;
2 /

Type created.

SQL> CREATE TABLE table_name (column_name NUMBER (10));

Table created.

SQL> DECLARE
2 v_array array_type :=
3 array_type (
4 number_table_type (1, 2, 3),
5 number_table_type (1, 2, 3, 4, 5),
6 number_table_type (1, 2),
7 number_table_type (1, 2, 3, 4));
8 BEGIN
9 FORALL i IN v_array.FIRST .. v_array.LAST
10 EXECUTE IMMEDIATE
11 'DECLARE ' ||
12 ' v_table number_table_type := :1;' ||
13 'BEGIN ' ||
14 ' FORALL j IN v_table.FIRST .. v_table.LAST ' ||
15 ' INSERT INTO table_name (column_name) ' ||
16 ' VALUES (v_table (j)); ' ||
17 'END; '
18 USING v_array (i);
19 END;
20 /

PL/SQL procedure successfully completed.

SQL> SELECT *
2 FROM table_name;

COLUMN_NAME
-----------
1
2
3
1
2
3
4
5
1
2
1
2
3
4

14 rows selected.

SQL>

Answer:
Wow! Thanks for bringing this to my attention, James.
I hadn't really given any thought to using FORALL in this way. Upon reflection, it makes sense (well, it had better, hadn't it?), as an anonymous block is considered a special case, within Oracle, of a SQL statement. It certainly expands the horizons of what is possible with a FORALL statement!

No comments:

Post a Comment