Obtaining Query Count Without EXECUTE-ing a Query

Obtaining a count of records that will be retrieved by EXECUTE_QUERY before actually performing it in a database block is especially useful when the requirement is to prevent navigation to a block when query hits are zero. A typical scenario of such a situation is when the detail block records exist on a separate canvas not visible on Form startup and the user is required to click a Details button to see them. Giving an alert message such as No Details exist when the user clicks the Details button is more meaningful than displaying a blank details screen, when no details exist for the chosen parent record.

The technique given here avoids two performance issues. First, you do not want to perform a SELECT COUNT(*) from the corresponding base table mainly for performance reasons. Second, using :SYSTEM.LAST_QUERY and executing it dynamically using DBMS_SQL cause a bottleneck by executing the query on the server side explicitly, thus involving more trips.

The solution is to do a COUNT_QUERY and get the QUERY_HITS for the corresponding block immediately following the COUNT_QUERY. The following function does the job:

FUNCTION query_count (p_block_name VARCHAR2) RETURN NUMBER

IS

cnt NUMBER;

BEGIN

GO_BLOCK(p_block_name);

COUNT_QUERY;

cnt := GET_BLOCK_PROPERTY(p_block_name, QUERY_HITS);

IF FORM_SUCCESS THEN

 RETURN (cnt);

ELSE

 MESSAGE('Error in getting Query Hits for block ':SYSTEM.CURRENT_BLOCK);

 RAISE FORM_TRIGGER_FAILURE;

END IF;

END;

The preceding function can be called in the appropriate trigger, such as WHEN-BUTTEN-PRESSED, to achieve the desired functionality.

The following WHEN-BUTTON-PRESSED trigger is defined for the Details button. It initially invokes the above query_count function to obtain the count of detail records for a particular master record. If this count is zero it throws an alert to indicate No Details exist. Otherwise, control navigates to the detail block and does an EXECUTE_QUERY.

WHEN-BUTTON-PRESSED trigger of 'Details'button



DECLARE

 v_cnt NUMBER;

BEGIN

 v_cnt := query_count();

 IF (v_cnt = 0) THEN

 p_show_alert('No Details exist.');

 ELSE

 GO_BLOCK();

 EXECUTE_QUERY;

 END IF;

END;

This technique involves two tasks :

• COUNT_QUERY is necessary to initiate the QUERY_HITS property of the block and should be immediately before the GET_BLOCK_PROPERTY statement.
• Oracle Forms displays the message FRM-40355: Query will display 0 records when the query hits are zero as obtained by a call to COUNT_QUERY. This should be suppressed in an ON-MESSAGE trigger by using the following code:

  if message_type = 'FRM'and message_code = 40355 then
  
   null;
  
  else
  
   message(message_type'-'to_char(message_code)': 'message_text);
  
  end if;