Using cursor with where in condition

Question

I am passing arguments `EBN,BGE' into a procedure , then I am passing this argument to a cursor.

create or replace procedure TEXT_MD (AS_IDS  VARCHAR2)
is
CURSOR C_A (AS_ID  VARCHAR2) IS
       SELECT
       name
       FROM S_US
       WHERE US_ID IN (AS_ID);


BEGIN


 FOR A IN C_A (AS_IDS) LOOP

 DBMS_OUTPUT.PUT_LINE('I got here: '||AS_IDS);

end loop;


 END;

But while debuging the count of the cursor is still null

So my question , why the cursor not returning values with in condition

Answer 1

You are passing a string parameter, so it will be used as a string, not as a list of strings; so, your cursor will be something like

SELECT name
FROM S_US
WHERE US_ID IN ('EBN,BGE')

This will, of course, not do what you need. You may need to change your procedure and the way to pass parameters; if you want to keep a string parameter , one way could be the following:

setup:

SQL> CREATE TABLE S_US
  2  (
  3      US_ID,
  4      NAME
  5  ) AS
  6      SELECT 'EBN', 'EBN name' FROM DUAL
  7      UNION ALL
  8      SELECT 'BGE', 'BGE name' FROM DUAL;

Table created.

procedure:

SQL> CREATE OR REPLACE PROCEDURE TEXT_MD_2(AS_IDS VARCHAR2) IS
  2      vSQL varchar2(1000);
  3      c sys_refcursor;
  4      vName varchar2(16);
  5  BEGIN
  6      vSQL := 'SELECT name
  7                FROM S_US
  8               WHERE US_ID IN (' || AS_IDS || ')';
  9      open c for vSQL;
 10      loop
 11          fetch c into vName;
 12          if c%NOTFOUND then
 13              exit;
 14          end if;
 15          DBMS_OUTPUT.PUT_LINE(vName);
 16      END LOOP;
 17  END;
 18  /

Procedure created.

You need to call it with a string already formatted to be a parameter list for IN:

SQL> EXEC TEXT_MD_2('''EBN'',''BGE''');
EBN name
BGE name

PL/SQL procedure successfully completed.

This is only an example of a possible way, and not the way I would do this. Among the reasons to avoud this kind of approach, consider what Justin Cave says: "that would be a security risk due to SQL injection and would have a potentially significant performance penalty due to constant hard parsing".

I believe you should better check how to pass a list of values to your procedure, rather then using a string to represent a list of strings. Here is a possible way to do the same thing with a collection:

SQL> CREATE OR REPLACE TYPE tabVarchar2 AS TABLE OF VARCHAR2(16)
  2  /

Type created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE TEXT_MD_3(AS_IDS tabVarchar2) IS
  2      vSQL                                    VARCHAR2(1000);
  3      c                                       SYS_REFCURSOR;
  4      vName                                   VARCHAR2(16);
  5  BEGIN
  6      FOR i IN (SELECT name
  7                  FROM S_US INNER JOIN TABLE(AS_IDS) tab ON (tab.COLUMN_VALUE = US_ID))
  8      LOOP
  9          DBMS_OUTPUT.PUT_LINE(i.name);
 10      END LOOP;
 11  END;
 12  /

Procedure created.

SQL>
SQL> DECLARE
  2      vList                                   tabVarchar2 := NEW tabVarchar2();
  3  BEGIN
  4      vList.EXTEND(2);
  5      vList(1)    := 'BGE';
  6      vList(2)    := 'EBN';
  7      TEXT_MD_3(vList);
  8  END;
  9  /
BGE name
EBN name

PL/SQL procedure successfully completed.

SQL>

Again, you can define collections in different ways, within a stored procedure or not, indexed or not, and so on; this is only one of the possible ways, not necessarily the best, depending on your environment, needs.