how to pass where in clause values in stored procedure in Oracle Sql-CodePudding

I have a procedure which takes parameter as a string in comma seperated format like "Saved,In Progress". Below procedure is working fine with on one status like "Saved". but not working with comma seprated values

create or replace PROCEDURE get_Sample_request (
    in_request_status IN VARCHAR2,
     out_cursor        OUT SYS_REFCURSOR
) AS
    sql_qry     VARCHAR2 (150);
    emp_tot     NUMBER(3);
BEGIN
    OPEN out_cursor FOR SELECT * from user_requests where request_status in (in_request_status);



END get_Sample_request;

CodePudding user response：

Use LIKE:

create or replace PROCEDURE get_Sample_request (
    in_request_status IN VARCHAR2,
     out_cursor        OUT SYS_REFCURSOR
) AS
    sql_qry     VARCHAR2 (150);
    emp_tot     NUMBER(3);
BEGIN
    OPEN out_cursor FOR
      SELECT *
      FROM   user_requests
      WHERE  ',' || in_request_status || ',' LIKE '%,' || request_status || ',%';
END get_Sample_request;
/

Or, pass in a collection:

create or replace PROCEDURE get_Sample_request (
    in_request_status IN  SYS.ODCIVARCHAR2LIST,
     out_cursor       OUT SYS_REFCURSOR
) AS
    sql_qry     VARCHAR2 (150);
    emp_tot     NUMBER(3);
BEGIN
    OPEN out_cursor FOR
      SELECT *
      FROM   user_requests
      WHERE  request_status IN (SELECT column_value FROM TABLE(in_request_status));
END get_Sample_request;
/

Or, split the string (slower):

create or replace PROCEDURE get_Sample_request (
    in_request_status IN  VARCHAR2,
     out_cursor       OUT SYS_REFCURSOR
) AS
    sql_qry     VARCHAR2 (150);
    emp_tot     NUMBER(3);
BEGIN
    OPEN out_cursor FOR
      SELECT *
      FROM   user_requests
      WHERE  request_status IN (SELECT REGEXP_SUBSTR(in_request_status, '[^,] ', 1, LEVEL)
                                FROM   DUAL
                                CONNECT BY LEVEL <= REGEXP_COUNT(in_request_status, '[^,] '));
END get_Sample_request;
/