Advanced Search

INSERT rows from BULK COLLECT with LIMIT clause

/ Published in: PL/SQL
Save to your folder(s)
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.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. PROCEDURE load_my_recs() IS
  2.  
  3.     rec_count PLS_INTEGER := 0;
  4.     loop_count PLS_INTEGER := 0;
  5.  
  6.     CURSOR source_cur IS
  7.         SELECT * FROM my_table;
  8.  
  9.     TYPE source_coll_t IS TABLE OF my_table%ROWTYPE
  10.         INDEX BY PLS_INTEGER;
  11.     local_coll source_coll_t;
  12.  
  13.     bulk_errors EXCEPTION;
  14.     PRAGMA EXCEPTION_INIT ( bulk_errors, -24381 );
  15. BEGIN
  16.     OPEN source_cur;
  17.  
  18.     LOOP
  19.         FETCH source_cur BULK COLLECT
  20.             INTO local_coll LIMIT 100;
  21.         EXIT WHEN local_coll.COUNT = 0;
  22.  
  23.         loop_count := 0;
  24.  
  25.         /* ****** Your extra processing here  ****** */
  26.  
  27.         FOR idx IN 1 .. local_coll.COUNT
  28.         LOOP
  29.             loop_count := loop_count + 1;
  30.             source_coll(rec_count + idx) := local_coll(idx);
  31.         END LOOP;
  32.         /* ****** Your extra processing here  ****** */
  33.  
  34.         /* ****** Column definitions must match exactly! ****** */
  35.         FORALL idx IN 1 .. local_coll.COUNT
  36.             INSERT INTO my_table VALUES local_coll(idx);
  37.  
  38.         rec_count := rec_count + loop_count;
  39.     END LOOP;
  40.  
  41.     CLOSE source_cur;
  42.  
  43.     DBMS_OUTPUT.PUT_LINE('Last loop count: ' || loop_count);
  44.     DBMS_OUTPUT.PUT_LINE('Total count: ' || rec_count);
  45. EXCEPTION
  46.     WHEN NO_DATA_FOUND THEN
  47.         DBMS_OUTPUT.put_line('load_my_recs - NO_DATA_FOUND');
  48.         RAISE NO_DATA_FOUND;
  49.     WHEN bulk_errors 
  50.     THEN 
  51.         DBMS_OUTPUT.put_line('EXCEPTIONS in FORALL, count: ' || SQL%BULK_EXCEPTIONS.COUNT)
  52.         FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
  53.         LOOP
  54.             /* The index value in the binding array that caused the error. */
  55.             DBMS_OUTPUT.put_line('  Error Index: ' || SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX);
  56.             /* The error code that was raised. 
  57.             Warning! Oracle stores this as a positive, not negative value.
  58.             */
  59.             DBMS_OUTPUT.put_line('    SQLCODE: ' || SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
  60.             /* ADD A BETTER ERROR-HANDLER HERE SO THEY ACTUALLY GET SEEN!  */
  61.         END LOOP;
  62.     WHEN INVALID_CURSOR THEN
  63.         DBMS_OUTPUT.put_line('INVALID CURSOR.  loop count: ' || loop_count || '.   Total count: ' ||
  64.             rec_count);
  65.         RAISE;
  66.     WHEN OTHERS THEN
  67.         DBMS_OUTPUT.put_line('OTHER EXCEPTION IN load_my_recs.  loop count: ' || loop_count || '.   Total count: ' ||
  68.             rec_count);
  69.         DBMS_OUTPUT.put_line('    EXCEPTION - ' || SQLCODE || ': ' ||
  70.                              SQLERRM);
  71.         RAISE;
  72. END load_my_recs;

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.