Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Limit in bulk collect and forall

Nag AswadhatiAug 18 2015 — edited Aug 19 2015

Hi Experts,

version details

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0

desc example1

---- ---- ------

LID       NUMBER

KID       NUMBER

I created a table example1 as above and inserted 1,00,000 rows with numeric values from 1 to 100000 rows with for loop.

By commenting FORALL statement I am able to execute below code and got below output.

If I un comment the FORALL statement it is not executing( session got hanged), please suggest me on this.

declare

type tabtype is table of number index by pls_integer;

l_tab tabtype;

cursor c1 is  select lid  FROM example1;

begin

open c1;

   loop

     fetch c1 BULK COLLECT INTO l_tab limit 10000;

 

  --   forall i in l_tab.first..l_tab.last SAVE EXCEPTIONS update example1 set kid = l_tab(i); 

      dbms_output.put_line('count'||l_tab.count);

    exit when l_tab.exists(1) = false;

   end loop;

close c1;

commit;

  EXCEPTION

   WHEN OTHERS

   THEN

      IF SQLCODE = -24381

      THEN

         FOR indx IN 1..SQL%BULK_EXCEPTIONS.COUNT

         LOOP

            DBMS_OUTPUT.put_line (

                  SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX

               || ': '

               || SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);

         END LOOP;

      ELSE

         RAISE;

      END IF;

end;

/

anonymous block completed

count10000

count10000

count10000

count10000

count10000

count10000

count10000

count10000

count10000

count10000

count0

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 16 2015
Added on Aug 18 2015
11 comments
4,232 views