Saturday, November 19, 2016

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 spread sheet 
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;

0 comments :

Post a Comment

Note: Only a member of this blog may post a comment.