CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
andres martinez
andres martinez

Reputation: 291

How to pass a string list to Stored Procedure In Olracle

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;

Upvotes: 1

Views: 93

Answers (1)

T.S.
T.S.

Reputation: 19330

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')

Upvotes: 0

Related Questions