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;

Execute Immediate in Oracle Reports 11g

Using the EXECUTE IMMEDIATE command directly in Reports is not possible, but you can work around this by creating a stored procedure in the database and then using this procedure in Reports. 

The database stored procedure can be created as follows: 
CREATE OR REPLACE PROCEDURE DYNAMIC_SQL(STMNT char) IS 
begin 
     BEGIN 
         EXECUTE IMMEDIATE stmnt; 
     exception 
         when others then 
             dbms_output.put_line('hello'); 
     end; 
end; 

Now, this procedure can be called in Reports. 
For example: 
In the BeforeParam Trigger, the Procedure can be called as: 
DYNAMIC_SQL('drop table TEST'); 
This will drop the table 'TEST' from the database.