I am loading a table with approx. 375,000 rows with a procedure that uses 3
nested cursor loops to retrieve the needed data (sample PL/SQL code below).
My problem is where/when to do commits. I want to commit approx. every 500 rows
inserted, but it appears that I am losing some records because of the placement
of my commits. Can someone give me the proper way to do this so I am sure not to
miss any inserts? Thanks much!

BEGIN proc
/* ...cursor variables, etc. defined here...
THREE cursors -- will be nested */

CURSOR sa_sp_cur (v_coy NUMBER)
IS
SELECT sa_id, sp_id, premise_id, account_id
FROM tableA
WHERE coy = v_coy
ORDER BY premise_id;

CURSOR account_cur (v_coy NUMBER, v_cur_account_id NUMBER)
IS
SELECT cust_id
FROM tableB
WHERE coy = v_coy
AND account_id = v_cur_account_id;

CURSOR person_cur (v_coy NUMBER, v_cur_cust_id NUMBER)
IS
SELECT person_id, life_support_ind
FROM tableC
WHERE coy = v_coy
AND person_id = v_cur_cust_id;

BEGIN

OPEN sa_sp_cur (v_coy);

LOOP
BEGIN
FETCH sa_sp_cur INTO
v_cur_sa_id,
v_cur_sp_id,
v_cur_premise_id,
v_cur_account_id;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE('Error on fetch from sa_sp_cur');
END;

EXIT WHEN sa_sp_cur%NOTFOUND;

OPEN account_cur (v_coy, v_cur_account_id);

LOOP
BEGIN
FETCH account_cur INTO
v_cur_cust_id;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE('Error on fetch from account_cur');
END;

EXIT WHEN account_cur%NOTFOUND;

OPEN person_cur (v_coy, v_cur_cust_id);

LOOP
BEGIN
FETCH person_cur INTO
v_cur_person_id,
v_cur_life_support_ind;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE('Error on fetch from person_cur');
END;

EXIT WHEN person_cur%NOTFOUND;

BEGIN
INSERT INTO tableD VALUES
(v_cur_premise_id,
v_cur_account_id,
v_cur_person_id,
v_cur_sp_id,
v_cur_sa_id,
v_cur_life_support_ind,
sysdate,
sysdate,
'Insert - Initial Load');

v_counter := v_counter + 1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error on insert into CISIF_ACTIVE_PREMISE_LIST');
END;

END LOOP; /* End of person cursor loop */
CLOSE person_cur;

IF v_counter > 500 THEN
COMMIT;
v_counter := 0;
END IF;

END LOOP; /* End of account cursor loop */
CLOSE account_cur;

END LOOP; /* End of sa_sp cursor loop */
CLOSE sa_sp_cur;
COMMIT;
END proc;