This discussion is archived
4 Replies Latest reply: Feb 20, 2003 5:21 AM by 15280 RSS

Data Block record count

232913 Newbie
Currently Being Moderated
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
    195388 Newbie
    Currently Being Moderated
    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
    4157 Newbie
    Currently Being Moderated
    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
    385629 Newbie
    Currently Being Moderated
    For this u sud have Query_all record property set to Yes. Then write code in POST-QUERY

    IF :SYSTEM.LAST_RECORD = 'TRUE' THEN
    <Local Var> := TO_NUMBER(:SYSTEM.TRIGGER_RECORD);
    END IF;

    I hope, this is answer to ur question.
  • 4. Re: Data Block record count
    15280 Newbie
    Currently Being Moderated
    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:
    Count_Query;
    Execute_Query;

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

    WHEN-NEW-RECORD-INSTANCE
    :current_record := :System.Trigger_Record;

    This is it!
    Good luck!