CODEWITHSUNDEEP
sqloracle-databaseplsql
Eternal Noob
Eternal Noob

Reputation: 2807

comma separated parameter in plsql stored procedure

create or replace procedure PROC_MYDATA (inputStr IN VARCHAR2,     
  p_RecordSet      IN OUT SYS_REFCURSOR) is
begin
  OPEN p_RecordSet FOR
     (select * from myTable where name in (inputStr));
end PROC_MYDATA;

In the PLSQL Test window, I am trying to set,

inputStr = 'A','B'

and I am getting this error:

ORA-01722: invalid number

I also tried to put escape character for single quote.

inputStr = '''A''','''B'''

Same error.

Can someone please help me understand what am I doing wrong?

Upvotes: 0

Views: 1181

Answers (3)

user1413147
user1413147

Reputation: 21

This is faster

SELECT * from myTable where name in (select regexp_substr(inputStr,'[^,]+', 1, level) from dual
             connect by regexp_substr(inputStr, '[^,]+', 1, level) is not null);

Upvotes: 0

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

To use a list of comma-separated values you'll need to build and execute the statement dynamically:

create or replace procedure PROC_MYDATA (inputStr IN VARCHAR2,     
  p_RecordSet      IN OUT SYS_REFCURSOR)
is
  strSql  VARCHAR2(32767);
begin
  strSql := 'select * from myTable where name in (' || inputStr || ')';

  OPEN p_RecordSet FOR strSql;
end PROC_MYDATA;

You should use this with a string which contains the single-quote characters in it to delimit each string; thus, use

DECLARE
  inputStr  VARCHAR2(100);
  csrCursor SYS_REFCURSOR;
BEGIN
  inputStr = '''A'', ''B''';

  PROC_MYDATA(inputStr, csrCursor);

  -- ...code to use csrCursor;

  CLOSE csrCursor;
END;

Share and enjoy.

Upvotes: 1

David Faber
David Faber

Reputation: 12485

I'm afraid it doesn't work this way:

SELECT * from myTable where name in (inputStr);

You can use dynamic SQL, as in @Bob Jarvis' answer, or you can do the following:

SELECT * FROM myTable WHERE REGEXP_LIKE(name, '^(' || REPLACE(inputStr, ',', '|') || ')$');

The difficulty with the latter is that, in Oracle, a regular expression can be at most 512 bytes long. So your inputStr would be limited to 508 bytes (since we're adding four bytes for the anchors and the grouping).

Upvotes: 1

Related Questions