Oracle Technical Blogs: Report Triggers In Rdf Report

Report Triggers

Triggers are PL/SQL code, which gets executed automatically.

We have five types of triggers in Oracle reports.

1) Before Parameter form
2) After Parameter form
3) Before report
4) Between pages
5) After report

We cannot create new type of trigger.

Report triggers must explicitly return TRUE or FALSE

By looking at the names of the trigger, we understand at what point trigger is executed ( fired ).

Trigger Name	Explanation
Before Parameter form	Fires before the Runtime Parameter Form is displayed.
After Parameter form	Fires after the Runtime Parameter Form is displayed.
Before Report	Fires before the report runs but after query is parsed.
Between Pages	Fires before each page of the report is formatted, except the very first page.
After Report	Fires after you exit the Paper Design view.

Let us start understanding Before report and After report triggers

Open the report builder tool → Use the report wizard → OK → Next → Create both Web and Paper layout → Next → Style – Tabular → Next→ SQL Query → Data Source definition – select * from emp; → Next → provide username, password and database → Connect → Move all the columns from Available Fields to Displayed Fields → Next → Next → Next → Predefined Template – Blue → Next → Finish.

This is how the report look like

Go to Tools → Object Navigator → Report Triggers → Right click on BEFORE REPORT → PL/SQL Editor.

Provide the following code

function BeforeReport return boolean is
begin srw.message(10, ‘Welcome to my Report’);
return (TRUE);
end;

Click on compile.

You should get a message “Successfully compiled”

Click on close.

SRW — is a built-in package

message — is a procedure, used to display a message with number and text.

We are done with creating BEFORE REPORT trigger.

Now , let us create AFTER REPORT trigger.

Go to Tools → Object Navigator → Report Triggers → Right click on AFTER REPORT → PL/SQL Editor

Provide the following code

function AfterReport return boolean is
begin srw.message (15, ‘End of the report’);
return (TRUE);
end;

Click on compile.

You should get a message “Successfully compiled”

Click on close.

Save the report as EMP3.rdf

Run the report.

BEFORE REPORT triggers fires, we get the following output

Click on OK

We get the Report output.

Click on Close button.

Observe, AFTER REPORT trigger is not executed.

To experience AFTER REPORT trigger, Go to File → Print Preview

As expected, BEFORE REPORT trigger is executed, we get the following output

Click on OK

We get the report in the Previewer window.

Click on Close button in the previewer window.

After report trigger gets executed. We get the following output

Let us now experience BEFORE PARAMETER FORM and AFTER PARAMETER FORM triggers.

Open the report builder tool → Use the report wizard OK → Next → Create both Web and Paper layout → Next → Style – Tabular → Next → SQL Query → Data Source definition – select * from emp where deptno = :deptno ; → Next provide username, password and database Connect Move all the columns from Available Fields to Displayed Fields → Next → Next → Next → Predefined Template – Blue → Next → Finish

We get Runtime parameter form, waiting for the user input

Enter the value 10

We get the following output

Go to Tools → Object Navigator → Report Triggers → Right click on AFTER PARAMETER FORM →

PL/SQL Editor

Provide the following code

function AfterPForm return boolean is
begin
if :deptno is null then
:deptno :=10;
srw.message ( 12, ‘Parameter not provided – default value assigned’);
return (TRUE);
else return ( TRUE );
end if;
end;