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 — Yaroslav Shabalin
– Yaroslav Shabalin, Commented Dec 27, 2013 at 13:21
Can you please tell what Database do you use, Oracle 10g or 11g ? — Maheswaran Ravisankar
– Maheswaran Ravisankar, Commented Dec 27, 2013 at 13:48

Maheswaran Ravisankar · Accepted Answer · 2013-12-28 08:41:02Z

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
you created the types? prList and pr , I mentioned before the PL/SQL Block ?
It should work, I tried the same and it works. Can you update wht u have shortly.. in ur question itself?
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.

Yaroslav Shabalin · Accepted Answer · 2013-12-28 21:57:34Z

0

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;

edited Dec 28, 2013 at 21:57

Yaroslav Shabalin

1,6443 gold badges17 silver badges29 bronze badges

answered Dec 27, 2013 at 14:14

Ashish sinha

1481 gold badge2 silver badges9 bronze badges

Collectives™ on Stack Overflow

bind array in cursor using plsql

2 Answers 2

6 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Linked

Related