1

How I can pass a string list to stored procedure? this is my Stored Procedure:


CREATE OR REPLACE PROCEDURE sp_search (p_name     IN VARCHAR2,
                                       r_cursor      SYS_REFCURSOR)
AS
BEGIN
    OPEN cursor FOR SELECT name
                      FROM tableN
                     WHERE name IN (p_name);
END;

Example:

var c_ref refcursor; 
sp_search('Andy,Marty,miky',:c_ref);
print c_ref;
andres martinez
  • 281
  • 1
  • 4
  • 12

1 Answers1

0

I think, this should do it

CREATE OR REPLACE PROCEDURE sp_search (
    p_name     IN VARCHAR2,
    r_cursor   OUT  SYS_REFCURSOR)
AS
Declare
    v_sql  VARCHAR2(200); 
BEGIN

    v_sql := 'SELECT name FROM table WHERE name IN (' || p_name || ')';
    OPEN r_cursor FOR v_sql;

END;

Only make sure that when you pass Andy,Marty,miky, you add ' so that final sql looks like

SELECT name FROM table WHERE name IN ('Andy','Marty','miky')
T.S.
  • 18,195
  • 11
  • 58
  • 78
  • 2
    Be aware that this approach means that your procedure is vulnerable to SQL injection attacks. And will fail when you search for, say, "O'Malley" because of the embedded single quote. – Justin Cave Feb 26 '19 at 04:01
  • @JustinCave Well.. I think, principal question was "how to..." Yes, we can pass array of values and use 'USING', with dynamically created bind vars. That will take longer to answer. Hopefully, OP can get this basis and run with it further – T.S. Feb 26 '19 at 05:27