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.
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.
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.
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);
I hope, this is answer to ur question.
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:
:total_records := Get_Block_Propety('BLOCK_NAME', Query_Hits);
:current_record := :System.Trigger_Record;
This is it!