Posted By

stews on 09/17/08

Tagged

Versions (?)

Last Edited at 09/17/08 05:11pm

Statistics

Viewed 863 times

Favorited by 1 user(s)

Related snippets

INSERT rows from BULK COLLECT with LIMIT clause

/ Published in: PL/SQL

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.

Highlighted TextExpand

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;

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.