SQL*Forms Tech Tips - Counting Records

From: Daniel Druker <ddruker_at_us.oracle.com>
Date: Tue, 26 May 1992 02:21:34 GMT
Message-ID: <1992May26.022134.29156_at_oracle.us.oracle.com>


Posted without permission... Yet another bulletin available directly from Oracle Support.

COUNTING QUERIED RECORDS CLEVERLY             Steve Muench, Tools Sup.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~          
One of the most frequently asked questions regarding SQL*Forms 3.0 involves counting the number of records retrieved by a query in a database block. SQL*Forms has no programmatic functionality which automatically performs this task. That is, even the most careful study of the Designer's Reference manual will not turn up a packaged function like:
                      ~~~
            NUMBER_OF_RECORDS_IN('blockname')
 

Nor will wishful thinking enhance the BLOCK_CHARACTERISTIC function with such an desirable argument as:  

BLOCK_CHARACTERISTIC('Blockname', NUMBER_OF_QUERIED_RECORDS)  

There is good reasoning for this, however. Runform fetches records one at a time from the cursor defined by the query criteria on any given block. It has no way of knowing in advance how many records a particular set of criteria will retrieve. At best, it can tell us how many records it has *fetched* from the cursor so far. But this is often not exactly what we are looking for.  

The complication, of course, in calculating the number of records retrieved by a database-block query is that in most cases the operator is allowed to enter adhoc query criteria. So a simple SELECT COUNT(*) FROM BASETABLE will not accurately give us the value we seek. We do have at our disposal the actual SQL statement that was executed at query time (including the operator's adhoc query conditions) in the :SYSTEM.LAST_QUERY variable, but since PL/SQL does not support dynamic SQL, we are hardpressed to cleverly use the information contained therein for our own devices.  

So typically, we resort to a combination of the EXECUTE_QUERY(ALL_RECORDS) and LAST_RECORD package procedures to first force all records in the query to be fetched, and then to position ourselves on the last one to subsequently take a peek at the :SYSTEM.CURSOR_RECORD which then corresponds precisely to the number of records returned. This works efficiently for queries which return a "reasonably small" number of records, but is impractical to count thousands of records.  

How many times have you pressed the [Count Query Hits] key and seen the "Query will retrieve 189 records." message only to say to yourself, "Gosh, I wish I could get that number of records into a variable somewhere!" Well, it is possible. Read on.  

The truth is that SQL*Forms *does* provide us a way to count the number of records returned by a query: the familiar [Count Query Hits] key, and its packaged procedure counterpart COUNT_QUERY. The only problem is that it produces a nice message containing that even *nicer* number that we so desperately want, but gives us no way to retrieve that number into a variable or into a field on our form. We can beat Forms at it's own game, however, by remembering that SQL*Forms 3.0 provides a useful new capability of trapping error and informational messages with the ON-ERROR and ON-MESSAGE triggers. Using these we can personally manage the type and text of the errors that our user sees.  

In particular, the message "Query will retrieve 189 records." is an informational message like any other. It corresponds to FRM-40355, and (since it is informational) will cause the ON-MESSAGE trigger to fire, if present. The special packaged functions MESSAGE_TYPE, MESSAGE_CODE, and MESSAGE_TEXT give us information on precisely which information message has caused our ON-MESSAGE trigger to fire. In the case we are examining, these would be set accordingly:  

       Message_Type = 'FRM'
       Message_Code = 40355
       Message_Text = 'Query will retrieve 189 records.'
 

The careful reader will already see in which direction we are heading. We need only write ON-MESSAGE trigger which traps the above message, and extracts from it the number of records that the query will retrieve. Once we've stripped out that number, we can store it in a field, or in a GLOBAL variable, or put it wherever we like.  

Of course, if some other informational message happens to fire our ON-MESSAGE trigger -- for example, "No changes to commit." -- we must be careful to do our duty as Forms' designers and print that message to the screen. We only want to handle the FRM-40355 message in a special way. So, for all of the other messages our "Querying Counting Spy" must not blow his cover, and non-chalantly show the user the message as if no ON-MESSAGE trigger were present.  

The benefit to using this Message-Trapping technique is that SQL*Forms automatically constructs the SELECT COUNT(*) FROM BASETABLE query including all of the user's adhoc query criteria, in response to the COUNT_QUERY command, or the [Count Query Hits] key. If there are indexes present, they will be used normally to calculate the COUNT(*). This will undoubtedly be faster than fetching hundreds of data records, moving to the last record, and recording the record number.  

The COUNT_QUERY can be used in your triggers to cause the COUNT(*) to be calculated, and subsequently the ON-MESSAGE trigger to fire which will "save" the counted value for later use. Note that the COUNT_QUERY package procedure functions similarly to the EXECUTE_QUERY packaged procedure in that it clears the current block and performs a "blind query" on the current block. Used in this way, any additional query criteria to be imposed on the block will have to be set in a PRE-QUERY trigger, or through the ENFORCE-KEY attribute.  

This technique is slightly less useful if the operator is allowed to get into ENTER QUERY mode, unless the user presses the [Count Query Hits] key while in that mode. We cannot do an ENTER_QUERY immediately followed by a COUNT_QUERY since that would query the user's records then immediately clear them to perform the COUNT_QUERY. The best use of this trick would involve a simulated 'QUERY MODE' where you accept the query criteria in regular (data-entry) mode, and then use the following steps to do the query:  

  1. Save the relevant field values in GLOBAL variables
  2. CLEAR_BLOCK(NO_VALIDATE); COUNT_QUERY; EXECUTE_QUERY;
  3. Restore the relevant field values from GLOBALS into the DB-Block field in the PRE-QUERY trigger.

This will use the relevant field values as if they were the query criteria and, in addition, calculate the COUNT of the records matching the criteria. This occurs because both the COUNT_QUERY and the EXECUTE_QUERY fire the PRE-QUERY trigger, so additional criteria can be enfored there.  

Below is an example of an ON-MESSAGE trigger which will do the job. It is written to be at block-level, but would function equally well at form-level.  



/*
|| ON-MESSAGE trigger -- to be put at block-level or form
|| level -- which can get the number of records to be retrieved
|| by a query into a GLOBAL variable, anytime a COUNT_QUERY
|| packaged procedure is executed.

||
||

*/
DECLARE   /*
  || Take a "snapshot" of the Message_Type, Message_Code, and
  || Message_Text variables that tell us exactly *which*
  || message we are about to trap.
  */
  l_msg_type       CHAR(3)           := MESSAGE_TYPE;                           
  l_msg_code       NUMBER            := MESSAGE_CODE;                           
  l_msg_text       CHAR(80)          := MESSAGE_TEXT;                           
  /*
  || Declare the following constants:
  || -------------------------------
  ||    COUNT_QUERY_HITS  -- The numerical code corresponding to the
  ||                         FRM message which announces:
  ||                         "Query will retrieve <n> records."
  ||    LETTERS           -- All of the letters we want to strip from
  ||                         the above Forms message to leave behind
  ||                         just the numerical part of the message.
  ||    NUMBERS           -- All of the digits that we want to keep
  ||                         from the above Forms message.
  */
  COUNT_QUERY_HITS CONSTANT NUMBER   := 40355;                                  
  LETTERS          CONSTANT CHAR(30) := '. ABCDEFGHIJKLMNOPQRSTUVWXYZ';         
  NUMBERS          CONSTANT CHAR(10) := '0123456789';                           
BEGIN                                                                           
  /*
  || If the Forms message that was about to appear on the screen 
  || is precisely the "Query will retrieve <n> records." message (FRM-40355)
  || then we want to do some further processing with the message text.
  */
  IF ( l_msg_code = COUNT_QUERY_HITS ) THEN                                     
     /*
     || Using the handy Translate function, we map the
     || the characters in the Message Text as follows:
     ||
     ||       <---- NUMBERS ----> <------- LETTERS ------->
     ||       '0 1 2 3 4 5 6 7 8 9 A B C D E F G ... '      "FROM" Map
     ||       '0 1 2 3 4 5 6 7 8 9'                         "TO"   Map
     ||
     || We map the digits to digits, and the letters to nothing.
     || By not supplying corresponding characters in the "TO" Map
     || for the alphabetic characters, we effectively map them to NULL,
     || which is another way of saying that we strip them from the
     || string.
     ||
     || The next effect is that from the message: 
     ||         "Query will retrieve 999 records"
     || we strip out everything except the number 999 (arguably the
     || most interesting part.
     */
 
     :GLOBAL.COUNT_QUERY := TRANSLATE( UPPER(l_msg_text),                       
                                       NUMBERS||LETTERS,                        
                                       NUMBERS);                                
  /*
  || Otherwise, we have to print out the informative message that RUNFORM
  || was about to print anyway. We put just the right punctuation between
  || the Message TYPE, CODE, and TEXT so the user cannot tell the 
  || difference.
  */
  ELSE                                                                          
     MESSAGE(l_msg_type||'-'||to_char(l_msg_code)||': '||l_msg_text);           
     RAISE FORM_TRIGGER_FAILURE;                                                
  END IF;                                                                       
END;                                                                            
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 

And below is an example of how you could modify the standard Master/Detail procedure called QUERY_DETAILS (3.0.16+) to include a call to COUNT_QUERY, before doing its normal EXECUTE_QUERY. In conjunction with the ON-MESSAGE trigger above, this could be used to always have a count of the detail records in the GLOBAL.COUNT_QUERY variable after changing master records.                                                                                 


      /*
      || Slightly modified version of the "Standard"
      || MASTER/DETAIL procedure 'Query_Details'. We've
      || added a single line to the procedure which
      || will cause a count of the detail records to
      || be taken automatically every time we query the
      || detail block in response to changing the
      || master record.
      */
      procedure query_details ( detail char ) is
      begin
          go_block(detail);
          check_package_failure;
 
          /*
          || This 'Count_Query' packaged procedure call
          || will cause our ON-MESSAGE trigger above to
          || fire since it would normally emit the message
          || "Query will retrieve <n> records." This way we
          || will always have a precise count of the detail
          || records stored in the global variable 
          || GLOBAL.COUNT_QUERY.
          */
          COUNT_QUERY;  
 
          execute_query;
      end;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 

So we've seen that by using a new feature of SQL*Forms 3.0, we can satisfy our desire to capture (FORMS calculates, WE capture) the number of records returned by a database block query. Never give up on a SQL*Forms problem, until you're truly convinced that there is no way to do it! Then think one more time, since there just might be a non-obvious, but easily available means right on hand to the trick.  

Have fun!

NOTE: Versions of SQLFORMS prior to 3.0.16.7 may present problems with ~~~~ the examples in this article.

  • Dan

Daniel Druker
Senior Consultant
Oracle Corporation                    


| Dan Druker                    |  work 415.506.4803                          |
| oracle*mail ddruker.us1       |  internet: ddruker_at_us.oracle.com            |
-------------------------------------------------------------------------------

Disclaimer: These are my opinions and mine alone, and don't reflect the views or position of my employer. Received on Tue May 26 1992 - 04:21:34 CEST

Original text of this message