Oracle Forms - how to speed up POST_QUERY trigger?

Question

This is code I'm dealing with:

declare
    --some cursors here

begin
    if some_condition = 'N' then
        raise form_trigger_failure;
    end if;

    --some fetches here
end;

It's from post-query trigger and basically my problem is that my block in Oracle forms returns 20k rows and post-query trigger is firing for each of the rows. Execution takes couple of minutes and I want to speed it up to couple of seconds. Data is validated in some_condition (it is a function, but it returns value really fast). If condition isn't met, then form_trigger_failure is raised. Is there any way to speed up this validation without changing the logic? (The same number of rows should be returned, this validation is important)

I've tried to change block properties, but it didn't help me. Also, when I deleted whole if statement, data was returned really fast, but it wasn't validated and there were returned rows that shouldn't be visible.

I don't think that the POST-QUERY trigger should be fired for every rows in your block when you open your Form module It should stop when the fetch limit has been reached (e.g. 50 rows) and be fired again when you fetch the next batch of rows. — R. Du

Littlefoot Littlefoot · Accepted Answer · 2019-04-16T06:58:54

Data is validated in some_condition ...

That's OK; but, why would you perform validation in a POST-QUERY trigger? It fetches data that already exist in the database, so it must be valid. Otherwise, why did you store it in the first place?

POST-QUERY should be used to populate non-database items.

Validation should be handled in WHEN-VALIDATE-ITEM or WHEN-VALIDATE-RECORD triggers, not in POST-QUERY.

I suggest you split those two actions. If certain parts of code can/should be shared between those two types of triggers, put it into a procedure (within a form) and call it when appropriate.

By the way, POST-QUERY won't fire for all 20K rows (unless you're buffering that much rows, and - if you do - you shouldn't).

Moreover, saying that the function returns the result really fast - probably, if it runs for a single row. Let it run on 200, 2000, 20000 rows as

select your_function(some_parameters) 
from that_table
where rownum < 2;  --> change 2 to 200 to 2000 to 20000 and see what happens

On the other hand, what's the purpose in fetching 20.000 rows? Who's going to review that? Are you sure that this is the way that you should be doing it? If so, consider switching to a stored procedure; let it perform those validations within the database, and let the form fetch "clean" data.

Oracle Forms - how to speed up POST_QUERY trigger?

1 Answers