I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.
So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.
Ready? Here it goes:
11g Feature: PLS-00436 Restriction in FORALL Statements Removed
In 11g, the PLS-00436 restriction has been removed, meaning individual elements of a collection can be referenced with SET and WHERE clauses in a FORALL construction.
Please check the following example, setting text of my_table to ‘Line x’ where x is the line number (also ID column):
DECLARE TYPE t_test IS TABLE OF my_table%ROWTYPE; l_test t_test; BEGIN SELECT * BULK COLLECT INTO l_test FROM my_table; FOR i IN l_test.first .. l_test.last LOOP l_test(i).text := 'Line ' || i; END LOOP; FORALL i IN l_test.first .. l_test.last UPDATE my_table SET text = l_test(i).text WHERE id = l_test(i).id; COMMIT; END; /