Validating Parameters in Oracle Reports using PL/SQL

Parameters can be populated and validated using various srw pl/sql triggers.

The following gives examples of: 

Validation trigger in parameter form 
Before parameter form trigger
After parameter form trigger
Before report trigger   

Examples of validation triggers on the property sheet for parameter 

PARAM_SAL. 

Query: select * from emp where sal > :PARAM_SAL 

These functions validate just this one trigger. The validation occurs when 

the user hits next field after inputting a value for the parameter. When the 

trigger is failed it returns to the parameter form.

Example 1:

----------------------------------------------------------------------------

This trigger aborts the report execution if no rows match the query criteria 

once the user has entered a value for param_sal.

----------------------------------------------------------------------------

function PARAM_SALValidTrigger return boolean is

hold_count number(4);

hold_sal  number(10);

begin

  hold_sal := :param_sal;

  select count(*) into hold_count from emp where sal > hold_sal; 

  if hold_count = 0 then

     srw.message(001,'this report returns no employees');

     raise srw.program_abort;

  end if;

  return(true);

end;

Example 2

--------------------------------------------------------------------------

In this trigger the users value for param_sal is compared to the maximum 

salary in the EMP table. If it is greater the report execution is aborted.

example query for your report: select * from emp where sal >= :parm_sal

--------------------------------------------------------------------------

function PARAM_SALValidTrigger return boolean is

hold_max number(10);

begin

  select max(sal) into hold_max from emp;

  if :param_sal > hold_max then

     srw.message(002,'SAL must be equal to or less than MAX(SAL)= '||

     to_char(hold_max));

     raise srw.program_abort;

  end if;

  return(true);

end;

Example 3

------------------------------------------------------------------------------

'Before parameter form' triggers can be used set up the environment for the

report e.g. create a table. It can also be used to supply default parameter 

values. This function populates the initial value of the parameter param_sal 

with the lowest salary value from the emp table.

------------------------------------------------------------------------------

function BeforePForm return boolean is

min_sal number(10);

begin

  select min(sal) into min_sal from emp;

  :param_sal := min_sal;

  return(true);

end;

Example 4

----------------------------------------------------------------------

'After parameter form' triggers can be used to validate a combination of 

parameters. Failing results in a return to the PARAMETER FORM.

Query: select * from emp where job=:jb and deptno=:dt

---------------------------------------------------------------------

function  AfterPForm return boolean is

begin

  if (:dt = 20) and (:jb = 'MANAGER') then 

     srw.message(003,'cannot report on Managers in Dept 20');

     raise srw.program_abort;

  end if;

  return(true);

end;

Example 5

-----------------------------------------------------------------------------

'Before report triggers' can be used to validate a combination of parameters.

The example below is the same as the after parameter form trigger above 

other than on failure return is passed to the MAIN MENU.

A 'Before Report Trigger' is executed right before formatting the report,

that is after initializing all internal structures, opening all SQL cursors

etc. In other words, after 'compiling' the report definition.

A second use of this trigger may be to launch a number of other reports

using the SRW.RUN_REPORT procedure.

-----------------------------------------------------------------------------

function BeforeReport return boolean is

begin

  if (:dt = 20) and (:jb = 'MANAGER') then 

     srw.message(004,'cannot report on Managers in Dept 20');

     raise srw.program_abort;

  end if;

  return(true);

end;