How to Show a Record Count Before Scrolling in Oracle Forms

After a query, the Oracle Forms status line shows Record: 1/? until the user scrolls to the last record, because Forms fetches rows only as they are needed. Users would rather see "Patient 1 of 15" straight away.

This guide shows how to count the records of a query before it runs in Oracle Forms 14.1.2, with COUNT_QUERY and QUERY_HITS, and how to show the current position in a counter item.

Sample Form for This Guide

The examples and screenshots use the sample form CH39_FIND from the Oracle Forms code repository on GitHub. Download it, open it in Forms Builder, and connect as CAREWELL to follow along.

The forms run against the CareWell Clinic sample schema, which you install first.

Count Before the Query

COUNT_QUERY counts the rows that the block's next query would return, using the block's current WHERE clause, without fetching them. It leaves the number in the block property QUERY_HITS.

Put the count just before EXECUTE_QUERY, in the procedure that queries the block. These are the lines from the search procedure of the sample form.

Count the rows, then query:

  go_block('PATIENTS');
  :system.message_level := '25';         -- hide FRM-40355, the message of COUNT_QUERY (level 25)
  count_query;
  :system.message_level := '0';
  :ctl.total := get_block_property('PATIENTS', QUERY_HITS);
  execute_query;

The total goes into CTL.TOTAL, an item of the control block that sits on no canvas.

Hide the Count Message

COUNT_QUERY reports its result as a message.

Output:

FRM-40355: Query will retrieve 15 records

A counter makes that message redundant. FRM-40355 has severity level 25, so setting :SYSTEM.MESSAGE_LEVEL to 25 around the call hides it, and setting it back to 0 restores normal messages. In a test, a first version that set the level to 5 still showed the message: the level must be at least the message's own severity.

Message levels and other ways to manage messages are covered in how to handle errors in Oracle Forms using ON-ERROR.

Show the Position in Each Record

WHEN-NEW-RECORD-INSTANCE fires every time the cursor enters a record, which makes it the place to update the counter.

Example (WHEN-NEW-RECORD-INSTANCE trigger on block PATIENTS):

if :patients.patient_id is null then
  :ctl.counter := null;
else
  :ctl.counter := 'Patient ' || :system.cursor_record || ' of ' || :ctl.total;
end if;

:SYSTEM.CURSOR_RECORD is the number of the current record. An empty record, such as the one after the last, clears the counter.

What the Count Costs

• A second query. COUNT_QUERY runs SELECT COUNT(*) with the same WHERE clause. That is worth it for a search screen, but not for a block that users page through one record at a time.
• A snapshot. The count is taken when the user searches, so rows added by other users afterwards are not counted until the next search.
• No recount on sorting. Changing the order with ORDER_BY does not change the number of rows, so the counter stays right without counting again.

Conclusion

To show a record count before the user scrolls, call COUNT_QUERY just before EXECUTE_QUERY, read the total from QUERY_HITS, and hide FRM-40355 by raising :SYSTEM.MESSAGE_LEVEL to 25 around the call. Update the counter in WHEN-NEW-RECORD-INSTANCE from :SYSTEM.CURSOR_RECORD. The price is one extra COUNT(*) query per search.