Loop over datablock extremely slow (oracle forms, pl/sql)

Question

I created a small loop to select the maximum value from a datablock in Oracle Forms. I have to do it this way, because the block sometimes gets global parameters from another form, or sometimes it has a different default where clause etc. It gets populated from different sources, so I can't create a cursor or I have to do it dynamically.

The loop I have, is declared like this:

loop
exit when :system.last_record = 'TRUE';
if (:block.number > v_max) then
    v_max := :block.number;
end if;
next_record;
end loop;

Why is it so slow? It takes a long time to even check a block with 10 records.

Or is there an easier way to select the maximum from a column in a block?

Thanks in advance,

Answer 1

You probably have a lot of calculations and additional fetches in your post_query trigger? This gets executed for each row.

Alternatively you could set the block parameter "FETCH ALL RECORDS=true", and in the post_query trigger you update a global variable (which you have initialized with 0 in the pre-query trigger).

e.g. pre_query:

:gobal.maxvalue := 0;

post_query (note this gets executed for each row):

    if :block.number > :gobal.maxvalue then   
           :gobal.maxvalue := :block.number;
    end if;
    if :system.last_record = 'TRUE' then
       do something with :global.maxvalue;  -- we are on the last record of the query, so do something with the max value
    end if;

after that you can use the global variable