Use of variable with comma seperated value in Oracle SQL Query

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_list 

The 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;