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;

No comments:

Post a Comment

Provide your valuable comments