Oracle: Dynamic query with IN clause using cursor

Question

Could some one help me with creating a example, to implement a dynamic query that uses IN clause and populate the results to cursor. The input parameter could be array or string concatenated.

I have been trying a lot but no successful.

Thanks..

Please refer this: I am also having this issue. stackoverflow.com/questions/34497184/… — Rajesh D
– Rajesh D, Commented Dec 29, 2015 at 15:10

Maheswaran Ravisankar · Accepted Answer · 2014-02-11 07:49:15Z

2

Clasic situation everyone has. You can form a Query string dynamically based on your array or sometthing. And use as OPEN CURSOR. .

  DECLARE
    v_mystring VARCHAR(50);
    v_my_ref_cursor sys_refcursor;
    in_string varchar2='''abc'',''bcd''';
    id2 varchar2(10):='123';
        myrecord tablename%rowtype;
  BEGIN

    v_mystring := 'SELECT a.*... from tablename a where name= :id2 and 
                    id in('||in_string||')';

    OPEN v_my_ref_cursor FOR v_mystring USING id2;

    LOOP
      FETCH v_my_ref_cursor INTO myrecord;
      EXIT WHEN v_my_ref_cursor%NOTFOUND;
        ..
      -- your processing
    END LOOP;
    CLOSE v_my_ref_cursor;

  END;

IN clause supports maximum of 1000 items. You can always use a table to join instead. That table might be a Global Temporary Table(GTT) whose data is visible to thats particular session.

Still you can use a nested table also for it(like PL/SQL table)

TABLE() will convert a PL/Sql table as a SQL understandable table object(an object actually)

A simple example of it below.

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.*
              from yourtable a
                   TABLE(CAST(myPrList as prList)) my_list
             where 
                   a.pr = my_list.pr
             order by a.pr 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;
/

edited Feb 11, 2014 at 7:49

answered Feb 11, 2014 at 7:05

Maheswaran Ravisankar

17.9k6 gold badges50 silver badges69 bronze badges

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

11 Comments

Dotnet_Ferrari Over a year ago

i wanted a dynamic sql, that i can assign it to a variable and that should contain in-clause. Also the cursor query should be OPEN p_Cursor FOR v_Query USING p_Id, p_StartDt p_Id contains multiple comma values

Maheswaran Ravisankar Over a year ago

hope, the first approach would interest you! do some looping to process your array and form a query string. in my above approach, you can also use the USING, but you cannot bind the IN clause... as it is not supported..

Dotnet_Ferrari Over a year ago

first looks good and easy, but not suitable for my implementation. I need to pass multiple parameters and one of their parameter has multiple ids

Maheswaran Ravisankar Over a year ago

multiple parameters is acheivable.. where as multiple ids you mean, you have a array of ids?

Dotnet_Ferrari Over a year ago

yes, i can pass as array from .net I was passing a table type and i get error when passing array or type value parameter

|

Collectives™ on Stack Overflow

Oracle: Dynamic query with IN clause using cursor

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related