2 Replies Latest reply: Jul 24, 2013 2:56 PM by Ariean RSS

    FORALL Statement Restriction

    Ariean

      I read the following statement from Oracle documentation and come up with following example which executes fine, but as per the statement it should fail, isn't it?

       

      Statement:

      • Within a FORALL loop, you cannot refer to the same collection in both the SET clause and the WHERE clause of an UPDATE statement. You might need to make a second copy of the collection and refer to the new name in the WHERE clause.


      Example:

      DECLARE
         TYPE NumList IS TABLE OF NUMBER;
         depts NumList := NumList(10, 20, 30, 40);
      BEGIN
      dbms_output.put_line(depts.FIRST);
      dbms_output.put_line(depts.last);
         FORALL i IN depts.FIRST..depts.LAST
           update emp set deptno=depts(i) where deptno =depts(i);  -- causes an error
           commit;
      END;
      

       

      Thank you