Report triggers execute PL/SQL functions at specific times during the execution and formatting of your report. Using the conditional processing capabilities of PL/SQL for these triggers, you can do things such as customize the formatting of your report, perform initialization tasks, and access the database. To create or modify a report trigger, you use the Report Triggers node in the Object Navigator. Report triggers must explicitly return TRUE or FALSE.
Oracle Reports has five global report triggers. You cannot create new global report triggers. The trigger names indicate at what point the trigger fires:
Before Report trigger |
Fires before the report runs but after queries are parsed. |
After Report trigger |
Fires after you exit the Paper Design view, or after report output is sent to a specified destination, such as a file, a printer, or an e-mail ID. This trigger can be used to clean up any initial processing that was done, such as deleting tables. Note, however, that this trigger always fires, whether or not your report completed successfully. |
Between Pages trigger |
Fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting. In the Paper Design view, this trigger only fires the first time that you go to a page. If you subsequently return to the page, the trigger does not fire again. |
Before Parameter Form trigger |
Fires before the Runtime Parameter Form is displayed. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns. If the Runtime Parameter Form is suppressed, this trigger still fires. Consequently, you can use this trigger for validation of command line parameters. |
After Parameter Form trigger |
Fires after the Runtime Parameter Form is displayed. From this trigger, you can access parameters and check their values. This trigger can also be used to change parameter values or, if an error occurs, return to the Runtime Parameter Form. Columns from the data model are not accessible from this trigger. If the Runtime Parameter Form is suppressed, the After Parameter Form trigger still fires. Consequently, you can use this trigger for validation of command line parameters or other data. |
Order of report trigger execution
The order of events when a report is run is as follows:
Before Parameter Form trigger is fired.
Note: If the Parameter Form is used on the Web, the Before Parameter
Form trigger fires twice: once when the Parameter Form is displayed, and
a second time when the parameters are submitted. This is because Oracle
Reports executes in a stateless fashion. There is no session to return to,
so the Before Parameter Form trigger has to fire the second time to ensure
that the parameters selected on the Parameter Form and passed on the command
line are valid.
Runtime Parameter Form appears (if not suppressed).
After Parameter Form trigger is fired (unless the user cancels from the Runtime Parameter Form).
Report is "compiled".
Queries are parsed.
Before Report trigger is fired.
SET TRANSACTION READONLY
is executed (if specified with the
READONLY
command line keyword or setting).
The report is run and the Between Pages trigger fires for each page
except the first one. (Note that data can be fetched at any time while the
report is being formatted.) COMMIT
s can occur during this time
due to: SRW.DO_SQL
with DDL, or if ONFAILURE
=COMMIT
,
and the report fails.
COMMIT
is executed (if READONLY
is specified)
to end the transaction.
After Report trigger is fired.
COMMIT
/ROLLBACK
/NOACTION
is executed
based on what was specified with the ONSUCCESS
command line keyword or setting.
In steps 4 through 9, avoid DDL statements that would modify the tables on which the report is based. Step 3 takes a snapshot of the tables and the snapshot must remain valid throughout the execution of the report. In steps 7 through 9, avoid DML statements that would modify the contents of the tables on which the report is based. Queries may be executed in any order, which makes DML statements unreliable (unless performed on tables not used by the report).
If you specify READONLY
on the command line, you should avoid DDL altogether. When you execute a
DDL statement (for example, with SRW.DO_SQL
),
a COMMIT
is automatically issued. If you are using READONLY
,
this will prematurely end the transaction begun by SET TRANSACTION
READONLY
.
As a general rule, any processing that will affect the data retrieved by the report should be performed in the Before Parameter Form or After Parameter Form triggers. (These are the two report triggers that fire before anything is parsed or fetched.) Any processing that will not affect the data retrieved by the report can be performed in the other triggers.
Consistency is guaranteed if you use DML or DDL in (or before) the After Form Trigger. However, consistency is not guaranteed in the Before Report trigger, since Oracle Reports may have to start work on data cursors before that trigger based on the definition of the report. Before the Before Report trigger, Oracle Reports describes the tables involved and opens cursors. Any change to the tables after that will not be seen by the report.
If you are sending your report output to the Paper Design view or Previewer,
you should note that some or all of the report triggers may be fired before
you see the report output. For example, suppose that you use SRW.MESSAGE
to issue a message in the Between Pages trigger when a condition is met.
If there are forward references in the report (for example, a total number of pages
displayed before the last page), Oracle Reports may have to format ahead
to compute the forward references. Hence, even though you have not yet seen
a page, it may already have been formatted and the trigger fired.
In report triggers, you can use the values of report-level columns and
parameters. For example, you might need to use the value of a parameter
called COUNT1
in a condition (for example, IF :COUNT1 = 10
).
Note, though, that you cannot reference any page-dependent columns (that is,
a column with a Reset At property set to Page) or columns that rely on page-dependent
columns.
In the Before and After Parameter Form, and Before and After Report triggers,
you can set the values of parameters (for example, give them a value in an assignment
statement, :COUNT1 = 15
). In the Before and After Report triggers,
you can also set the values of report-level, placeholder columns.
In the Between Pages trigger, you cannot set the values of any data model objects. Note also that the use of PL/SQL global variables to indirectly set the values of columns or parameters is not recommended. If you do this, you may get unpredictable results.
A lexical reference cannot be used to create additional bind variables
after the After Parameter Form trigger fires. For example, suppose you have
a query like the following (note that the WHERE
clause is replaced
by a lexical reference):
SELECT ENAME, SAL FROM EMP
&WHERE_CLAUSE
If the value of the WHERE_CLAUSE
parameter contains a reference
to a bind variable, you must specify the value in the After Parameter Form
trigger or earlier. You would get an error if you supplied the following
value for the parameter in the Before Report trigger. If you supplied this
same value in the After Parameter Form trigger, the report would run.
WHERE SAL = :new_bind
Copyright © 1984, 2005, Oracle. All rights reserved.