using variable with set of values inside pl/sql cursor

Question

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?

There is nothing special with comma in Oracle. Any special meaning derives purely from context. Your cursor defines a single variable. Which may just happen to contain one or more commas, like this sentence itself, but it is still 1 sentence not three. You will have to parse the string into its components. See how to split a comma separated string. — Belayer
– Belayer, Commented Feb 18, 2021 at 18:39

0xdb · Accepted Answer · 2021-02-18 20:11:23Z

2

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

answered Feb 18, 2021 at 20:11

0xdb

3,7071 gold badge23 silver badges39 bronze badges

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

Comments

Littlefoot · Accepted Answer · 2021-02-18 19:54:23Z

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>

Collectives™ on Stack Overflow

using variable with set of values inside pl/sql cursor

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related