multi value of oracle Procedure

Question

I want add multi value with p_so_code in Oracle 11g where SO in (A,B,C).

    PROCEDURE get_one_so (
         p_SO_CODE in String,
         RES OUT SYS_REFCURSOR
    )
        AS
    BEGIN
        OPEN RES FOR 

       SELECT
            so.so_code as SOCODE,
            cus.customer_code as CUSTOMERCODE,            
            cus.customer_name as CUSTOMERNAME,
            cus.email as EMAIL,            
            so.receiver_name as RECEIVERNAME,
            so.receiver_phone as RECEIVERPHONE,
            so.receiver_address as RECEIVERADDRESS,
            so.commment as COMMMENT,
            s.name as STAFFCREATE,     
                        to_char( so.CREATED_DATE,'dd/mm/yyyy') as CREATEDDATE,
            st.name as STAFFUPDATE,
            to_char(so.updated_date,'dd/mm/yyyy') as UPDATEDATE,
            sapp.name as STAFFAPPROVE,
           status,            
                so.reason_reject as REASONREJECT,
            so.payment as PAYMENY,
            to_char(so.send_date,'dd/mm/yyyy')   as SENDDATE,
            to_char(so.sended_date,'dd/mm/yyyy')   as SENDEDATE,
            so.staff_comment as STAFFCOMMENT

                     FROM

            saleorders so join customer cus on so.customer_code = cus.customer_code
            left join saleorderDetail sod on so.so_code = sod.so_code
            left join staff s on so.staff_create = s.staff_code
            left join staff st on so.staff_update = st.staff_code
            left join staff sapp on so.STAFF_APPROVE = sapp.staff_code
        WHERE
            so.SO_CODE in p_SO_CODE 
    END;

END package_saleoders;

I tried with replace (p_so_code,(','),(''',''')) but this didn't work.

Saying "this didn't work" isn't very helpful; it would be better to explain what actually happened and why that wasn't what you wanted. Does `p_so_code` have to be a string, or could you change the parameter's data type to a collection? — Alex Poole, Jul 11 '18 at 12:17
[Possible duplicate](https://stackoverflow.com/q/11041647/266304)? And [this might be helpful](https://stackoverflow.com/a/21695311/266304) too, as it shows opening a ref cursor; and also how to use a collection. — Alex Poole, Jul 11 '18 at 12:22
Possible duplicate of [How to use parameters in a 'where value in...' clause?](https://stackoverflow.com/questions/11041647/how-to-use-parameters-in-a-where-value-in-clause) — Kevin Burton, Jul 11 '18 at 12:36

score 1 · Accepted Answer · answered Jul 11 '18 at 12:32

Break the comma separated values in p_so_code to return multi row values and then use it in your in clause

PROCEDURE get_one_so (
     p_SO_CODE in String,
     RES OUT SYS_REFCURSOR
)
    AS
BEGIN
    OPEN RES FOR 

   SELECT
        so.so_code as SOCODE,
        cus.customer_code as CUSTOMERCODE,            
        cus.customer_name as CUSTOMERNAME,
        cus.email as EMAIL,            
        so.receiver_name as RECEIVERNAME,
        so.receiver_phone as RECEIVERPHONE,
        so.receiver_address as RECEIVERADDRESS,
        so.commment as COMMMENT,
        s.name as STAFFCREATE,     
                    to_char( so.CREATED_DATE,'dd/mm/yyyy') as CREATEDDATE,
        st.name as STAFFUPDATE,
        to_char(so.updated_date,'dd/mm/yyyy') as UPDATEDATE,
        sapp.name as STAFFAPPROVE,
       status,            
            so.reason_reject as REASONREJECT,
        so.payment as PAYMENY,
        to_char(so.send_date,'dd/mm/yyyy')   as SENDDATE,
        to_char(so.sended_date,'dd/mm/yyyy')   as SENDEDATE,
        so.staff_comment as STAFFCOMMENT
    FROM
        saleorders so join customer cus on so.customer_code = cus.customer_code
        left join saleorderDetail sod on so.so_code = sod.so_code
        left join staff s on so.staff_create = s.staff_code
        left join staff st on so.staff_update = st.staff_code
        left join staff sapp on so.STAFF_APPROVE = sapp.staff_code
    WHERE
        so.so_code IN (SELECT regexp_substr(p_so_code, '[^,]+', 1, LEVEL) 
                         FROM dual 
                      connect by regexp_substr(p_SO_CODE, '[^,]+', 1, level) is not null);

END;

multi value of oracle Procedure

1 Answers1