Revision: 8377
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
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