Geek asks and answers

Mass collection using "to update"

I ran into an interesting and unexpected problem when processing records in Oracle (11g) using BULK COLLECT.

The following code worked perfectly, processing all millionth records without problems:

-- Define cursor
cursor My_Data_Cur Is
Select col1
      ,col2
from My_Table_1;
…

-- Open the cursor
open My_Data_Cur;

-- Loop through all the records in the cursor
loop

  -- Read the first group of records
  fetch My_Data_Cur
  bulk collect into My_Data_Rec
  limit 100;

  -- Exit when there are no more records to process
  Exit when My_Data_Rec.count = 0;

  -- Loop through the records in the group
  for idx in 1 .. My_Data_Rec.count
  loop
    … do work here to populate a records to be inserted into My_Table_2 …
  end loop;

  -- Insert the records into the second table
  forall idx in 1 .. My_Data_Rec.count
  insert into My_Table_2…;

  -- Delete the records just processed from the source table
  forall idx in 1 .. My_Data_Rec.count
  delete from My_Table_1 …;

  commit;
end loop;

Since at the end of processing each group of 100 records (the limit is 100), we delete the records that we just read and processed, although it would be nice to add the syntax “for updating” to the cursor definition so that another process could not update any of the records between the time the data was read and the time the record was deleted.

So the only thing that I changed in the code was ...

cursor My_Data_Cur
is
  select col1
        ,col2
from My_Table_1
for update;

PL/SQL , 100 , . , , " " , .

, " " ? , ? , , , .

,

+3
2

, .

My_Data_Cur for update, Oracle My_Data_1, . commit, Oracle (, Oracle, ). , Oracle , for update. , 0 .

, commit . , . , - , 100 (.. rownum <= 100), , , 100, , .

+1

.

. , Exception .

!

, . , , .

Error report -
ORA-01002: fetch out of sequence
ORA-06512: at line 7
01002. 00000 -  "fetch out of sequence"
*Cause:    This error means that a fetch has been attempted from a cursor
           which is no longer valid.  Note that a PL/SQL cursor loop
           implicitly does fetches, and thus may also cause this error.
           There are a number of possible causes for this error, including:
           1) Fetching from a cursor after the last row has been retrieved
           and the ORA-1403 error returned.
           2) If the cursor has been opened with the FOR UPDATE clause,
           fetching after a COMMIT has been issued will return the error.
           3) Rebinding any placeholders in the SQL statement, then issuing
           a fetch before reexecuting the statement.
*Action:   1) Do not issue a fetch statement after the last row has been
           retrieved - there are no more rows to fetch.
           2) Do not issue a COMMIT inside a fetch loop for a cursor
           that has been opened FOR UPDATE.
           3) Reexecute the statement after rebinding, then attempt to
           fetch again.

, rowid

Docs:

DECLARE
-- if "FOR UPDATE OF salary" is included on following line, an error is raised
   CURSOR c1 IS SELECT e.*,rowid FROM employees e;
   emp_rec  employees%ROWTYPE;
BEGIN
   OPEN c1;
   LOOP
     FETCH c1 INTO emp_rec; -- FETCH fails on the second iteration with FOR UPDATE
     EXIT WHEN c1%NOTFOUND;
     IF emp_rec.employee_id = 105 THEN
       UPDATE employees SET salary = salary * 1.05 WHERE rowid = emp_rec.rowid;
         -- this mimics WHERE CURRENT OF c1
     END IF;
     COMMIT;  -- releases locks
   END LOOP;
END;
/

! , ROWID AND COMMIT, !

Bulk Binding.

+1

More articles:


All Articles