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;

Create form with Cancel Query option

Set the form level property Interaction Mode to Blocking or non-Blocking.
When set to non-Blocking, a dialog box appears that contains the following prompt:

Press cancel to end this database operation

When a long running query is executed in forms, this enables the option to interrupt the query by pressing the cancel button. However, this only works for Forms blocks which are based on standard tables or views.
It does not work for blocks based on stored procedures, where the data is returned by a ref cursor or a table of records.

Enable Cancel Query option on Oracle Forms

Sometimes when we query form with higher data results the forms hangs and leaves us with option to wait until data is displayed or close it forcefully.
Using profile option FND: Enable Cancel Query, the cancel button with message "Press cancel to end this database operation" can be displayed that allows canceling query. The profile option may not support all forms but still you can have this option for most of the oracle provided forms.