Posted By

stews on 09/17/08


Tagged

loop limit bulk collect


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

stews


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.

  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. local_coll source_coll_t;
  11.  
  12. bulk_errors EXCEPTION;
  13. PRAGMA EXCEPTION_INIT ( bulk_errors, -24381 );
  14. OPEN source_cur;
  15.  
  16. FETCH source_cur BULK COLLECT
  17. INTO local_coll LIMIT 100;
  18. EXIT WHEN local_coll.COUNT = 0;
  19.  
  20. loop_count := 0;
  21.  
  22. /* ****** Your extra processing here ****** */
  23.  
  24. FOR idx IN 1 .. local_coll.COUNT
  25. loop_count := loop_count + 1;
  26. source_coll(rec_count + idx) := local_coll(idx);
  27. /* ****** Your extra processing here ****** */
  28.  
  29. /* ****** Column definitions must match exactly! ****** */
  30. FORALL idx IN 1 .. local_coll.COUNT
  31. INSERT INTO my_table VALUES local_coll(idx);
  32.  
  33. rec_count := rec_count + loop_count;
  34.  
  35. CLOSE source_cur;
  36.  
  37. DBMS_OUTPUT.PUT_LINE('Last loop count: ' || loop_count);
  38. DBMS_OUTPUT.PUT_LINE('Total count: ' || rec_count);
  39. DBMS_OUTPUT.put_line('load_my_recs - NO_DATA_FOUND');
  40. WHEN bulk_errors
  41. DBMS_OUTPUT.put_line('EXCEPTIONS in FORALL, count: ' || SQL%BULK_EXCEPTIONS.COUNT)
  42. FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
  43. /* The index value in the binding array that caused the error. */
  44. DBMS_OUTPUT.put_line(' Error Index: ' || SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX);
  45. /* The error code that was raised.
  46.   Warning! Oracle stores this as a positive, not negative value.
  47.   */
  48. DBMS_OUTPUT.put_line(' SQLCODE: ' || SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
  49. /* ADD A BETTER ERROR-HANDLER HERE SO THEY ACTUALLY GET SEEN! */
  50. DBMS_OUTPUT.put_line('INVALID CURSOR. loop count: ' || loop_count || '. Total count: ' ||
  51. rec_count);
  52. DBMS_OUTPUT.put_line('OTHER EXCEPTION IN load_my_recs. loop count: ' || loop_count || '. Total count: ' ||
  53. rec_count);
  54. DBMS_OUTPUT.put_line(' EXCEPTION - ' || SQLCODE || ': ' ||
  55. END load_my_recs;

Report this snippet  

You need to login to post a comment.