11g Feature: PLS-00436 Restriction in FORALL Statements Removed

Hi All!
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;
/
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s