View Suresh Vaishya's profile
The requirement is to pass comma seperated value to a procedure and to use that variable in the query to extract values. For e.g. variable p_ord_num_list has a value of '90001234, 90001235, 90001236' and we attempt to use this in variable in the query as below
SELECT * FROM oe_order_headers_all WHERE order_number IN p_ord_num_listThe above query completes in error
ORA-01722: invalid number ORA-06512: at line 13
The above requirement can be achieved in following way
DECLARE
p_ord_num_list VARCHAR2 (4000) := '90001234, 90001235, 90001236';
BEGIN
FOR i IN (SELECT order_number
FROM oe_order_headers_all e
WHERE order_number IN (
SELECT EXTRACTVALUE (xt.COLUMN_VALUE, 'e')
FROM TABLE (XMLSEQUENCE (EXTRACT (XMLTYPE ( '<ord_num><e>'
|| REPLACE (p_ord_num_list, ',', '</e><e>')
|| '</e></ord_num>'
)
, '/ord_num/*'
)
)
) xt))
LOOP
DBMS_OUTPUT.put_line ('a = ' || i.order_number);
END LOOP;
END;The way above statement works is that it first generates the XML tag for each comma seperated value and then extracts values from each element.
The other way to do this is by using regular expression functions as shown below
DECLARE
p_ord_num_list VARCHAR2 (4000) := '90001234, 90001235, 90001236';
BEGIN
FOR i IN (SELECT * FROM oe_order_headers_all WHERE order_number IN (
SELECT TRIM(REGEXP_SUBSTR(p_ord_num_list , '[^,]+', 1, LEVEL)) item_id
FROM (SELECT p_ord_num_list str
FROM DUAL)
CONNECT BY LEVEL <= LENGTH(str) - LENGTH(REPLACE(str, ',')) + 1))
LOOP
DBMS_OUTPUT.put_line ('a = ' || i.order_number);
END LOOP;
END;
