4 Replies Latest reply: Feb 20, 2003 1:21 PM by 15280 RSS

    Data Block record count

      What is the most efficient/accurate method of returning the number of records that fulfill the conditions of the where clause of a data block when the query is executed. I am having timing problems between refreshing the data block and counting the records.
        • 1. Re: Data Block record count
          depending on how many rows you return, upon completing the query, issue the built-in subprogram called 'LAST_RECORD' and retrieve the value of the current record from the system built-in record variable, and return using 'First_Record' to the top of the query.

          Using sqlplus is a great way to give you and idea of how many rows a single query return to also give you some idea of how many rows are buffered in your data block.

          • 2. Re: Data Block record count
            I would suggest that you have a look at the online help for "calculated fields".
            Since Forms 6 you can create fields that automatically will count the number of records in any block.
            Its the simplest way, and you don't need to write any code for it.
            • 3. Re: Data Block record count
              For this u sud have Query_all record property set to Yes. Then write code in POST-QUERY

              <Local Var> := TO_NUMBER(:SYSTEM.TRIGGER_RECORD);
              END IF;

              I hope, this is answer to ur question.
              • 4. Re: Data Block record count
                This question has been asked many times.
                I always wonder, that everybody propose using of Last_Record, which is the slowest way to count the upcomming records. It is event slower than to have an explicit "select count(1) from table_name" somewhere.

                For this purpose Oracle Forms supports a special Block property called "Query_Hits". The property is beeing set if the built-in COUNT_QUERY is executed in "ON-COUNT" trigger and also is executed before EXECUTE_QUERY. So, you have to have 2 triggers and the code to fire the count

                to fire the count do:

                ON-COUNT trigger:
                :total_records := Get_Block_Propety('BLOCK_NAME', Query_Hits);

                :current_record := :System.Trigger_Record;

                This is it!
                Good luck!