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.

Lalit Kumar B · Accepted Answer · 2015-01-06 11:01:23Z

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 6, 2015 at 11:01

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Andromeda Over a year ago

Thank you... I was trying it the other way l_orders.ORD_ID(indx)

Andromeda Over a year ago

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)'

Lalit Kumar B Over a year ago

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.

Andromeda Over a year ago

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...

Dev1 Over a year ago

@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 ? stackoverflow.com/questions/66605055/…

Exhausted · Accepted Answer · 2015-01-06 11:04:54Z

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 6, 2015 at 11:04

Exhausted

1,8932 gold badges23 silver badges33 bronze badges

Collectives™ on Stack Overflow

Oracle PLSQL BULK Collect and For Loop

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related