Validating Parameters in Oracle Reports using PL/SQL with Example

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;

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.