Oracle PLSQL BULK Collect and For Loop

Question

I have written the following oracle procedure to fetch data in bulk and process it in blocks. I am using the bulk collect option with limit to fetch the data. But inside for loop i am not able to retrieve the ORD_ID. I am trying to output the ORD_ID using

DBMS_OUTPUT.put_line(l_orders(indx));

But getting compilation error "wrong number or type of arguments in call to 'PUT_LINE'"

create or replace PROCEDURE TESTPROC AS 

CURSOR order_id_cur IS SELECT ORD_ID FROM orders ORDER BY ORD_ID ASC;
 l_order_id   VARCHAR2(100);
 TYPE orders_aat IS TABLE OF order_id_cur%ROWTYPE;
 l_orders orders_aat;
 limit_in NUMBER      :=10; 
 batch_in NUMBER :=0;


BEGIN
  OPEN order_id_cur;
  LOOP
    FETCH order_id_cur 
        BULK COLLECT INTO l_orders LIMIT limit_in;
    DBMS_OUTPUT.put_line('Batch-----'||batch_in);
    FOR indx IN 1 .. l_orders.COUNT 
    LOOP
        DBMS_OUTPUT.put_line(indx);
        DBMS_OUTPUT.put_line(l_orders(indx));
    END LOOP;

    EXIT WHEN l_orders.COUNT < limit_in;
    batch_in := batch_in+1;
   END LOOP;
   CLOSE order_id_cur;
END TESTPROC;

How can i get the values of ORD_ID inside the for loop.

score 5 · Accepted Answer · answered Jan 06 '15 at 11:01

5

Do it like this -

DBMS_OUTPUT.put_line(l_orders(indx).ORD_ID);

For example,

SQL> DECLARE
  2  type t
  3  IS
  4    TABLE OF emp%rowtype;
  5    a t;
  6  BEGIN
  7    SELECT * BULK COLLECT INTO a FROM emp;
  8    FOR i IN 1..a.count
  9    LOOP
 10      dbms_output.put_line (a(i).ename);
 11    END LOOP;
 12  END;
 13  /
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL procedure successfully completed.

SQL>

answered Jan 06 '15 at 11:01

Lalit Kumar B

47,486
13
97
124

1

Thank you... I was trying it the other way l_orders.ORD_ID(indx) – Andromeda Jan 06 '15 at 11:06
One more doubt ...is it possible to fetch all the ORD_ID at once instead of using for loop and iterating through each. basically i want to use all the ord_ids in an 'IN' statement..eg 'delete from orders_bckup where ord_id in (allOrderIds)' – Andromeda Jan 06 '15 at 11:11
You can use a `FORALL` statement for `DELETE`. `row by row` is always `slow by slow`. But, why do you want to use `PL/SQL` when it could be done in `SQL`. You are unnecessarily introducing `context switching`. – Lalit Kumar B Jan 06 '15 at 11:24
1

Thanks...Will look into FORALL...Anyways the above proc is just a sample one..in my scenario i have to copy data from specific table to backup tables and delete the data from original table based on different conditions... – Andromeda Jan 06 '15 at 11:29
@Lalit Kumar B - Can we use a select statement within a bulk collect for loop and set the stored value in collection to the select query ? https://stackoverflow.com/questions/66605055/oracle-for-loop-does-not-iterate-in-sys-refcursor – Linnea Mar 13 '21 at 12:08

score 0 · Answer 2 · answered Jan 06 '15 at 11:04

0

you can also make loop directly to cursor like below

FOR recc in order_id_cur 
   LOOP
       DBMS_OUTPUT.put_line(recc.ORD_ID );
   END LOOP;

answered Jan 06 '15 at 11:04

Exhausted

1,867
2
23
33

Oracle PLSQL BULK Collect and For Loop

2 Answers2