CODEWITHSUNDEEP
oracle-databaseplsqlcursor
imaginecreate
imaginecreate

Reputation: 11

using variable with set of values inside pl/sql cursor

I am trying to use variable v_values with set of values inside cursor in the Where clause using IN operator but it returns no record.

create or replace PROCEDURE MyProc IS

   /* Cursor decleration */
   CURSOR CUR_DUMMY (v_values as varchar2)
   IS
        SELECT COL1,COL2,COL3 
          FROM TABLE 
             WHERE COL1 IN v_values;

   l_values varchar2();

BEGIN

   l_values:='(''one'',''two'',''three'')';

   FOR REC IN CUR_DUMMY (l_values)
   LOOP

   dbms.output.put_line(REC.col1 || ' ' || REC.col2 || ' ' || REC.col3);

   END LOOP;

END;

Any suggestion how to resolve this issue?

Upvotes: 0

Views: 1146

Answers (2)

0xdb
0xdb

Reputation: 3707

IN operator requires a list of values, not the values as comma delimited string.

One simple solution is using nested tables:

create table tab (col1,col2,col3) as
    select 'row'||rownum, 2, 3 from dual connect by level<=10
/
create or replace procedure proc is
    cursor cur (vl sys.odciVarchar2List) is
        select col1,col2,col3
        from tab
        where col1 in (select column_value val from table (vl));
begin
    for rec in cur (sys.odciVarchar2List ('row1','row3','row9')) loop
        dbms_output.put_line (rec.col1||' '||rec.col2||' '||rec.col3);
    end loop;
end;
/

SQL> exec proc
row1 2 3
row3 2 3
row9 2 3

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 143103

Here's an example based on Scott's EMP table (as I don't have yours, whose name is invalid anyway). Besides, you should pay attention to fix obvious errors, such as dbms.output (there's no dot but underline), varchar2() variable with no size, v_values as while declaring the cursor (there's no as) and similar stuff which are easy to fix and show that you're actually paying attention to what you're doing, and not typing just because you must.

Read comments within code.

SQL> set serveroutput on;
SQL> create or replace procedure myproc is
  2     cursor cur_dummy (v_values  varchar2) is
  3       -- split V_VALUES into rows
  4       select empno, ename, job
  5       from emp
  6       where job in (select trim(regexp_substr(v_values, '[^,]+', 1, level))
  7                     from dual
  8                     connect by level <= regexp_count(v_values, ',') + 1
  9                    );
 10     l_values varchar2(100);
 11  begin
 12     -- no need to complicate with single quotes and stuff; just name those values
 13     l_values:='CLERK, MANAGER';
 14
 15     for rec in cur_dummy (l_values)
 16     loop
 17       dbms_output.put_line(rec.empno || ' ' || rec.ename || ' ' || rec.job);
 18     end loop;
 19  end;
 20  /

Procedure created.

SQL> exec myproc;
7369 SMITH CLERK
7566 JONES MANAGER
7698 BLAKE MANAGER
7876 ADAMS CLERK
7900 JAMES CLERK

PL/SQL procedure successfully completed.

SQL>

Upvotes: 0

Related Questions