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!

Using Limit in Bulk Collect Correctly

3399237Feb 15 2017 — edited Feb 16 2017

Hi Everybody.

I am working on this one piece of the much larger PL/SQL program. It's just to deal with the deletes at the end of the script.

It's a procedure that runs very slow and generates tons of redo.

The table itself has around 500 Million rows, and that's one of the reasons but more than that the procedure was written when that table was much smaller.

Here is the Version of Oracle I am on:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Here is the test table I am using, basically here is the DDL.

CREATE TABLE TEST_SCH.BK_U_C_D_TEST

(

  GENERIC_ID          NUMBER                      NOT NULL,

  USER_ID                 NUMBER                      NOT NULL,

  CID                           NUMBER                      NOT NULL,

  NOTE                        VARCHAR2(4000 BYTE),

  CREATION_DATE     DATE                        NOT NULL,

  CREATED_BY            VARCHAR2(50 BYTE)           NOT NULL,

  LAST_UPDATE_DATE  DATE                        NOT NULL,

  LAST_UPDATED_BY   VARCHAR2(50 BYTE)           NOT NULL,

  OGG_KEY_ID              RAW(16),

  OGG_TS                      TIMESTAMP(6)

)

/

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

declare

cursor curr_rec is

select rowid rid

from TEST_SCH.BK_U_C_D_TEST where USER_ID ='123456789';

type brecord is table of rowid index by binary_integer;

brec brecord;

begin

open curr_rec;

FOR vqtd IN 1..500 loop

fetch c bulk collect into brec limit 20000;

forall vloop in 1 .. brec.count

delete from TEST_SCH.BK_U_C_D_TEST where rowid = brec(vloop);

exit when curr_rec%notfound;

commit;

dbms_lock.sleep(15);

end loop;

close curr_rec;

end;

/

For some reason I cannot get this to compile, even though I know from the syntax, it's correct, but when I run it it gives me this error...

ORA-06550: line 10, column 7:

PLS-00201: identifier 'C' must be declared

ORA-06550: line 10, column 1:

PL/SQL: SQL Statement ignored

Can someone please help me hammer this out? What do I need to do with the "c"??

Thanks in Advance!

Sandrine.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 16 2017
Added on Feb 15 2017
15 comments
2,792 views