bind array in cursor using plsql

Question

declare
  cursor lc is 
    select * 
      from (select a.lin, a.pr, 
                   b.name, sum(a.up) as u,
                   sum (a.d)         as d
              from li_dy_4 a,
                   p_list  b 
             where a.pr=b.id 
               and b.parent_id != 0 
               and a.partitionid <= 308 
               and a.partitionid >= 302 
               and a.pr in (91,80)
             GROUP BY a.pr, b.name, a.lin  
             order by d desc) ;
  rec lc%ROWTYPE;

BEGIN 
  open lc;
  loop 
    FETCH lc into rec;
    dbms_output.put_line(rec.pr);
    exit when lc%NOTFOUND;
  end loop;
  close lc;
END;

the above statement works fine for me. What I am not capable of finding anything hopeful is changing the value after the "in" statement which is a.pr in (91,80) I have listed the values here manually, but I want to pass it to the cursor as an array of numbers for a.pr column. In short I want to do a.pr = idlist wher idlist is an array. Please anyone tell me if my idea is possible.

possible duplicate of [Declaration of multiple values in Oracle BIND Variables](http://stackoverflow.com/questions/6632741/declaration-of-multiple-values-in-oracle-bind-variables) — Yaroslav Shabalin, Dec 27 '13 at 13:21
Can you please tell what Database do you use, Oracle 10g or 11g ? — Maheswaran Ravisankar, Dec 27 '13 at 13:48

score 2 · Accepted Answer · answered Dec 28 '13 at 08:41

Just want to remind you, the IN clause supports 1000 items only. And that could be the primary reason ,there's nothing called BULK BINDING for SELECT Queries. We have FORALL INSERT/UPDATE, which is like BULK BINDING. Unfortunately select has none.

But still you can achieve your requirement in a different fashion.

You can try a global temporary table(GTT) which a temporary table with "scope of the data inserted" is only to that session.

You can FORALL INSERT all your data for IN clause into that table, and join the TABLE to your Query.

Else you can have a nested table (if oracle 10g) or a simple pl/sql type itself (if oracle 11g), with all your IN class items as records and join it to your Query.

Example: Using NESTED TABLE , effective for less number(<10000) of items

CREATE TYPE pr AS OBJECT
           (pr  NUMBER);
/
CREATE TYPE prList AS TABLE OF pr;
/

declare
  myPrList prList := prList ();
  cursor lc is 
    select * 
      from (select a.lin, a.pr, 
                   b.name, sum(a.up) as u,
                   sum (a.d)         as d
              from li_dy_4 a,
                   p_list  b,
                   TABLE(CAST(myPrList as prList)) my_list
             where a.pr=b.id 
               and b.parent_id != 0 
               and a.partitionid <= 308 
               and a.partitionid >= 302 
               and a.pr = my_list.pr
             GROUP BY a.pr, b.name, a.lin  
             order by d desc) ;
  rec lc%ROWTYPE;

BEGIN 
  /*Populate the Nested Table, with whatever collection you have */
  myPrList := prList ( pr(91),
                       pr(80));
  /*
     Sample code: for populating from your TABLE OF NUMBER type 

     FOR I IN 1..your_input_array.COUNT
     LOOP
          myPrList.EXTEND;
          myPrList(I) := pr(your_input_array(I));
     END LOOP;
  */
  open lc;
  loop 
    FETCH lc into rec;
    exit when lc%NOTFOUND; -- Your Exit WHEN condition should be checked afte FETCH iyself!
    dbms_output.put_line(rec.pr);
  end loop;
  close lc;
END;
/

,I am using oracle11gR2.I have tried the solution you had provided and also my records are less than from what you have mentioned.But,am getting. PL/SQL: ORA-00904: "MY_LIST"."PR": invalid identifier PLS-00341: declaration of cursor 'LC' is incomplete or malformed....what shall i do — mohaskuar, Dec 30 '13 at 13:07
you created the types? prList and pr , I mentioned before the PL/SQL Block ? — Maheswaran Ravisankar, Dec 30 '13 at 13:26
It should work, I tried the same and it works. Can you update wht u have shortly.. in ur question itself? — Maheswaran Ravisankar, Dec 30 '13 at 14:10
my aim of using the above sample code is to create a procedure and pass variables and an array to the procedure.Execute that procedure from php by passing arguments. — mohaskuar, Jan 02 '14 at 07:29
yes, that is why i added a comment with a for loop using your_input_array. That could help you. — Maheswaran Ravisankar, Jan 02 '14 at 07:31

score 0 · Answer 2 · edited Dec 28 '13 at 21:57

I don't know the exact structure of your global table but you can use the collection in cursor like this

declare
 cursor c1 is
  select last_name ls  from empc;
 type x is table of employees.last_name%type;
 x1 x := x();
 cnt integer :=0;
begin
 for z in c1 loop
  cnt := cnt +1;
  x1.extend;
  x1(cnt) := z.ls;
  if x1(cnt) is NULL then-----------
   DBMS_OUTPUT.PUT_LINE('ASHISH');
  end if;
  dbms_output.put_line(cnt || ' '|| x1(cnt));
 end loop;
end;

bind array in cursor using plsql

2 Answers2