Commits with nested cursor loops-PL/SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Commits with nested cursor loops-PL/SQL

Hybrid View

  1. #1
    Join Date
    Jan 2001
    Posts
    63
    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;


  2. #2
    Join Date
    Dec 2000
    Posts
    126

    Arrow

    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
  •  


Click Here to Expand Forum to Full Width