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;
