Monday, March 21, 2016

Bulk collect and FOR ALL in PLSQL Oracle

DECLARE
   CURSOR c_orders
   IS
      SELECT   customer_id, product_id, business_date,
               SUM (price * quantity) sales_amount
          FROM orders
      GROUP BY customer_id, product_id, business_date;

   TYPE t_orders IS TABLE OF c_orders%TYPE;

   v_orders   t_orders;
     e_bulk_exception        EXCEPTION;
   PRAGMA EXCEPTION_INIT (e_bulk_exception, -24381);
BEGIN
   OPEN c_orders;

   LOOP
     BEGIN
      FETCH c_orders
      BULK COLLECT INTO t_orders LIMIT 50000;

      EXIT WHEN t_orders.COUNT = 0;
for all i in 1..t_orders.count SAVE EXCEPTIONS
            INSERT INTO daily_sales(customer_id, product_id, business_date,
                   sales_amount
                  )
                 VALUES (t_orders(i).customer_id, t_orders(i).product_id, t_orders(i).business_date,
                   t_orders(i).sales
                  );
         COMMIT;
      EXCEPTION
         WHEN e_bulk_exception
         THEN
           
               DBMS_OUTPUT.put_line (   'BULKCOEECT COUNT'
                                     || SQL%BULK_EXCEPTIONS.COUNT
                                    );

               FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
               LOOP
                  DBMS_OUTPUT.put_line
                                    (   'BULKCOEECT message '
                                     || SQL%BULK_EXCEPTIONS (i).ERROR_INDEX
                                     || SQL%BULK_EXCEPTIONS (i).ERROR_CODE
                                    );
               END LOOP;
       
      END;
   END LOOP;

   CLOSE c_orders;

   COMMIT;
END;

No comments:

Post a Comment