Link to home
Start Free TrialLog in
Avatar of SFMelange
SFMelangeFlag for United States of America

asked on

How do you pass a Default_Where Clause from a Oracel Form to a Oracle Report

-In a Report I have created a User Parameter P_Where
- In a Form I have created a Default_Where clause to display multiple records based off of a hit list, When-Button-Pressed.

declare
      v_where     varchar2(2000) := '1=1 ';
Begin
 --STN_Num The where clause will be either the wild card "% = like" or "equal"
  if :stn_query.stn_num is not null then
        if instr(:stn_query.stn_num,'%') > 0 then
              v_where := v_where || 'and stn_num like ''' ||:stn_query.stn_num || ''' ';
        else
              v_where := v_where || 'and stn_num = ''' || :stn_query.stn_num || ''' ';
        end if;
  end if;
etc.........................

set_block_property('STN_DISPLAY',DEFAULT_WHERE,v_where);

- In the same Form I have created a Parameter P_Where.  
  :parameter.p_where := v_where; --To pass the where clause to the report
 
  go_block('STN_DISPLAY');
  execute_query;
end;

In a When-Button-Pressed trigger I create the parameter list and pass
declare
      
      param_list      PARAMLIST;
      pl_name      varchar2(4000) := 'P_WHERE';
Begin

param_list := create_parameter_list(pl_name);
IF id_null(param_list) THEN  
        RAISE pl_not_created;
ELSE
------This is the part I need clarification. Do I add a Text_Parameter or a Data_parameter. What do I enter as the Value to pass to the report?
      add_parameter(param_list,'P_WHERE',TEXT_PARAMETER, ?);
           add_parameter(param_list,'P_WHERE',DATA_PARAMETER,?);

        RP2RRO.RP2RRO_RUN_PRODUCT(REPORTS,repfile,SYNCHRONOUS,RUNTIME,FILESYSTEM,param_list,null);
       DESTROY_PARAMETER_LIST(param_list);
       CLEAR_BLOCK;
END IF;


ASKER CERTIFIED SOLUTION
Avatar of Muhammad Ahmad Imran
Muhammad Ahmad Imran
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial