-
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;
-
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;
**********
IF v_counter > 500 THEN
COMMIT;
v_counter := 0;
END IF;
************
END LOOP; /* End of person cursor loop */
CLOSE person_cur;
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;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|