Friday, January 23, 2009

Undocumented Feature 11g BULK In-BIND

Really this is a FEATURE, not a bug. Prior to 11g if you tried to refer to a field of a bulk bind in a FORALL statement you would get an error:

PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND

In 11g this works just fine. Consider this simple example:

Connected to:
Oracle Database 11g Enterprise Edition Release - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
2 TYPE little_rt IS RECORD (
3 empno emp.empno%TYPE,
4 sal emp.sal%TYPE );
5 TYPE empno_sal_list_tt IS TABLE OF little_rt INDEX BY PLS_INTEGER;
6 the_list empno_sal_list_tt;
8 SELECT empno, sal BULK COLLECT INTO the_list FROM emp;
9 FOR idx IN 1 .. the_list.COUNT LOOP
10 the_list (idx).sal := (the_list (idx).sal) * .15;
12 FORALL idx IN 1 .. the_list.COUNT
13 UPDATE emp SET sal = the_list (idx).sal
14 WHERE empno = the_list (idx).empno;
15 dbms_output.put_line('Records updated: '||to_char(the_list.COUNT));
16 END;
17 /
Records updated: 14

PL/SQL procedure successfully completed.

Not sure why this is undocumented. Likely is just slipped thru the cracks. Maybe not as cool and market flashy as the Exadata is, but a nifty feature none the less.