Yeap. That alwasy helps. Use counter and commit when reached the commit point. Example:
SET ECHO OFF
SET TRANSACTION USE ROLLBACK SEGMENT BIG_ONE;
DECLARE
v_commit_point NUMBER := 1000;
v_error_code NUMBER;
v_error_message VARCHAR2(200);
v_id NUMBER;
CURSOR update_owner_cursor
IS
SELECT a.avs_structures_id,
a.ownername,
a.ownersource,
a.ownerdate,
a.address1,
a.address2,
a.address3,
a.address4,
a.ownerzip
FROM v_assessor_aoi_owner a, aoi_owners b
WHERE a.avs_structures_id = b.avs_structures_id
AND b.delete_date IS NULL
AND (a.ownername != b.owner_name
OR a.address3 != b.owner_street);
BEGIN
-- Update records in owners table
FOR update_owner_cursor_rec IN update_owner_cursor LOOP
BEGIN
UPDATE aoi_owners
SET delete_date = (SELECT sysdate FROM dual)
WHERE avs_structures_id = update_owner_cursor_rec.avs_structures_id
AND delete_date IS NULL;
v_id := update_owner_cursor_rec.avs_structures_id;
INSERT INTO aoi_owners
VALUES (aoi_owners_seq.nextval,
update_owner_cursor_rec.avs_structures_id,
update_owner_cursor_rec.ownername,
update_owner_cursor_rec.ownersource,
update_owner_cursor_rec.ownerdate,
update_owner_cursor_rec.address1,
update_owner_cursor_rec.address2,
update_owner_cursor_rec.address3,
update_owner_cursor_rec.address4,
update_owner_cursor_rec.ownerzip,
NULL, NULL);
IF update_owner_cursor%ROWCOUNT >= v_commit_point THEN
v_commit_point := v_commit_point * 2;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_error_code := SQLCODE;
v_error_message := SUBSTR (SQLERRM, 1, 200);
INSERT INTO aoi_conversion_exceptions
VALUES ('AOI_UPDATE_OWNER.SQL', 'AOI_OWNERS', 'UPDATE',
'V_ASSESSOR_AOI_OWNER - '||'AVS_STRUCTURES_ID = '||TO_CHAR(v_id),
v_error_code, v_error_message);
COMMIT;
END;
END LOOP;
END;
/
Hope that helps,
clio_usa - OCP DBA
------------------