Cursors with Transaction And Exception Handling

Back to Menu

Cursors with Transaction And Exception Handling



In below example TABLE_ONE has two columns and both represent Foreign Key of table TABLE_TWO. We will retrieve Foreign Keys from both Table in two cursors and then iterate over each cursor and update column_two of TABLE_TWO.



PL/SQL

set serveroutput ON size 1000000;

DECLARE
  i   INTEGER := 0;
---select all orders Layer 3
  CURSOR c1 IS
    select T1.ORDER_OBJECT_KEY from TABLE_ONE T1
    where T1.ORD_ORDER_ID in ('34324314','3414','43124','43','4134');
    
    CURSOR c2 IS
    select T1.STATE_OBJECT_KEY from TABLE_ONE T1
    where T1.ORD_ORDER_ID in ('34324314','3414','43124','43','4134');
    
    output VARCHAR2(20 CHAR);
BEGIN
   OPEN c1;  -- open cursor, Iterate over records and update TABLE_TWO WIRG NEW VALUE
   LOOP
      FETCH c1 INTO output;
      EXIT WHEN c1%NOTFOUND;
      UPDATE TABLE_TWO  SET COLUMN_TWO = 'NEW_VALUE'  WHERE id = output;
    i := i+1;
   IF mod(i, 1000) = 0 THEN    -- Commit every 1000 records
      COMMIT;
   END IF;
   END LOOP;
   COMMIT;
   CLOSe c1;
   i :=0;
   OPEN c2;  -- open cursor, Iterate over records and update descriptor in XML
   LOOP
      FETCH c2 INTO output;
      EXIT WHEN c2%NOTFOUND;
      UPDATE TABLE_TWO  SET COLUMN_TWO = 'NEW_VALUE'  WHERE id = output;
    i := i+1;
   IF mod(i, 1000) = 0 THEN    -- Commit every 1000 records
      COMMIT;
   END IF;
   END LOOP;
   COMMIT;
   CLOSe c2;
   EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE('Updates have been rolled back till last commit.');
END;
/
EXIT;

Back to Menu