Programmatically Canceling a Query

The capability to programmatically cancel a query was a much-felt need in Forms 4.5. This was needed for long-running queries in Forms data blocks with the option of canceling the query available to the end user . A get-around for this was to display an intermediate message like Query in progress while the query was running. Although this served as a temporary patch to account for the boredom of waiting till the query was finished, it did not provide the user with the capability of aborting the query before it completed execution.

Forms 5.0 introduced non-blocking SQL that eases the problem. I will discuss the solution with regards to Forms 4.5 and Forms 5.0 and higher separately.

In Forms 5.0 and above, set the Form level Interaction Mode property to Non-Blocking , and Forms will automatically bring up a Cancel alert (similar to that in SQL*Plus) whenever an EXECUTE_QUERY is performed, either interactively or programmatically.

You set the Interaction Mode property to Non-Blocking to prompt for breaking a long-running query. This might terminate either the SELECT or the FETCH phases of query execution.

The Cancel alert is invoked only when the minimum amount of time has elapsed since the query has been submitted for execution. This might include both SELECT and FETCH phases.

In Forms 6.0 and above, the Interaction Mode property can be set dynamically using SET_FORM_PROPERTY :

SET_FORM_PROPERTY(:SYSTEM.CURRENT_FORM, INTERACTION_MODE, value);

The value is either of the character strings BLOCKING or NON_BLOCKING.

In Forms 4.5, it is easy to simulate non-blocking SQL for the fetch phase. To do the same thing for the SELECT phase requires complex programming using the ORA_FFI foreign function interface. Although a timer provides asynchronous operation to a certain extent, using a timer will not work because the expiration of the timer is deferred till the execution of the query.

As an example, consider a base table block based on the PERSON table with 10,000 records. We will analyze two cases, a simple EXECUTE_QUERY and an EXECUTE_QUERY(ALL_RECORDS) (or setting the block property Query All Records to Yes).

An EXECUTE_QUERY in the block, either interactively or programmatically, might not bring up the Cancel alert if the query is executed before the minimum amount of time required for the alert to display; that is, for non-blocking SQL to prompt for blocking or breaking the query. This will be the result even if there are 10,000 records to be fetched and displayed. For, by default, the number of records fetched is equal to the number of records displayed.

During an EXECUTE_QUERY(ALL_RECORDS) operation, there is maximum likelihood that non-blocking SQL will bring up Cancel during the fetch phase. This is because the SELECT phase is nothing more than

SELECT * FROM person;

and can be very efficient. As a result, the time taken might be less than the minimum required for non-blocking SQL to take action. But the fetch phase takes longer, as it has to do a repeated fetch and buffering in the Forms side.

The preceding fetch phase might be simulated in Forms 4.5 as follows . In the POST-QUERY trigger, check the :SYSTEM.MODE system variable for QUERY. Then, bring up an alert with a message This operation is continuing on the database side. and include a CANCEL button.

Now, do an ABORT_QUERY based on the response:

POST-QUERY



DECLARE

 Alert_id Alert;

 Alert_button NUMBER;

BEGIN

 IF :SYSTEM.MODE = QUERY THEN

 Alert_button := SHOW_ALERT(alert_id);

 IF alert_button = ALERT_BUTTON1 THEN

 ABORT_QUERY;

 END IF;

 END IF;

END;

Here there are two special cases to be considered :

FRM-40350 Query caused no records to be entered.

FRM-40355 Query caused no records to be entered, Re-enter.

These messages are automatically taken care of as we try to block the fetch phase. Otherwise, there are two easy ways to track the number of records fetched:

• Execute COUNT_QUERY immediately followed by a GET_BLOCK_PROPERTY(, QUERY_HITS). This technique is described in Chapter 2, "Advanced GUI Development: Developing Beyond GUI."
• Write a PRE-SELECT trigger, pre-execute the query using :SYSTEM.LAST_QUERY, and execute it using DBMS_SQL on the database side.