Return to Snippet

Revision: 8377
at September 17, 2008 17:11 by stews


Initial Code
PROCEDURE load_my_recs() IS

    rec_count PLS_INTEGER := 0;
    loop_count PLS_INTEGER := 0;

    CURSOR source_cur IS
        SELECT * FROM my_table;

    TYPE source_coll_t IS TABLE OF my_table%ROWTYPE
        INDEX BY PLS_INTEGER;
    local_coll source_coll_t;

    bulk_errors EXCEPTION;
    PRAGMA EXCEPTION_INIT ( bulk_errors, -24381 );
BEGIN
    OPEN source_cur;

    LOOP
        FETCH source_cur BULK COLLECT
            INTO local_coll LIMIT 100;
        EXIT WHEN local_coll.COUNT = 0;

        loop_count := 0;

        /* ****** Your extra processing here  ****** */

        FOR idx IN 1 .. local_coll.COUNT
        LOOP
            loop_count := loop_count + 1;
            source_coll(rec_count + idx) := local_coll(idx);
        END LOOP;
        /* ****** Your extra processing here  ****** */

        /* ****** Column definitions must match exactly! ****** */
        FORALL idx IN 1 .. local_coll.COUNT
            INSERT INTO my_table VALUES local_coll(idx);

        rec_count := rec_count + loop_count;
    END LOOP;

    CLOSE source_cur;

    DBMS_OUTPUT.PUT_LINE('Last loop count: ' || loop_count);
    DBMS_OUTPUT.PUT_LINE('Total count: ' || rec_count);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        dbms_output.put_line('load_my_recs - NO_DATA_FOUND');
        RAISE no_data_found;
    WHEN bulk_errors 
    THEN 
        dbms_output.put_line('EXCEPTIONS in FORALL, count: ' || SQL%BULK_EXCEPTIONS.COUNT)
        FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
        LOOP
            /* The index value in the binding array that caused the error. */
            dbms_output.put_line('  Error Index: ' || SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX);
            /* The error code that was raised. 
            Warning! Oracle stores this as a positive, not negative value.
            */
            dbms_output.put_line('    SQLCODE: ' || SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
            /* ADD A BETTER ERROR-HANDLER HERE SO THEY ACTUALLY GET SEEN!  */
        END LOOP;
    WHEN INVALID_CURSOR THEN
        dbms_output.put_line('INVALID CURSOR.  loop count: ' || loop_count || '.   Total count: ' ||
            rec_count);
        RAISE;
    WHEN OTHERS THEN
        dbms_output.put_line('OTHER EXCEPTION IN load_my_recs.  loop count: ' || loop_count || '.   Total count: ' ||
            rec_count);
        dbms_output.put_line('    EXCEPTION - ' || SQLCODE || ': ' ||
                             SQLERRM);
        RAISE;
END load_my_recs;

Initial URL

                                

Initial Description
Reads rows from a source table using BULK COLLECT and LIMIT, then puts them in another collection (presumably with a different layout structure), then loads them to your destination table.

Initial Title
INSERT rows from BULK COLLECT with LIMIT clause

Initial Tags

                                

Initial Language
PL/SQL