Hi,

We have a requirement to query and display data in JSP pages and when clicked on 'Report' button of the JSP, an Oracle report should be called.

The data which is queried in JSP should be passed to the Oracle report. Is there any way of doing it? This is to avoid re-querying the database again from Oracle Reports.

Environment: jdk1.3, Oracle Application Server, Oracle 9i, Report 6i

Thanks in Advance,
Srinivas

Srinivas,
You should be able to use runtime.exec and invoke Oracle reports(oracle reports provides a command line executable) and pass the arguments you gather from jsp. In case you need to display the results of the report, command line of reports provides an option to generate output files of format html/pdf etc

Hope it helps
thanks
raghu komanduri

You can use RWCGI60.exe through web also. Submitting an apropriate url to RWCGI60.exe will run your report and return it in any format (html , pdf e.t.c.) you want. RWCGI.exe can be found at following addres. This works on IAS 1.0.2.0.0 version. 9IAS Release 2 might be different.
http://your_servers_ip/dev60cgi/rwcgi60

Hi,

We had old report using Oracle forms 3.O that call report with parameters. Can I do this in JSP.

sample code used by old form to call report. Please let me know What changes are needed.

PROCEDURE EXEC_REPORT
(p_Rep_Type IN CHAR) IS

 Param_List_ID Paramlist;
 v_Out_File    Varchar2(45);
 v_Where       Varchar2(45);
 v_Order_By    Varchar2(45);
 v_Title       Varchar2(45);
 v_DB_File     Varchar2(45);

BEGIN

MESSAGE ('Running Benefit Paid Report. Please Wait..', no_acknowledge);

IF BP_PKG.Zone_of_Adj IN ('SC', 'SM', 'SR', 'SV', 'SW') THEN
   v_DB_FILE  := 'ALL_PHIST';
ELSE
   v_DB_FILE  := 'PHIST_'||BP_PKG.Zone_of_Adj||'_F';
END IF;

IF :Report_Type ='A' THEN
   v_out_file  :=  'C:\USERDATA\'||:CLAIM_NUM||'_All_'||TO_CHAR(SYSDATE,'MMDDYY')||'.PDF';
   v_Where     := 'AND (PTC.BP_COMP=''Y'' OR PTC.BP_MEDICAL = ''Y'')' ;
   v_Order_By  := 'PTC.BP_COMP,PTC.BP_REPORT_ORDER,';
   v_Title     :=  Null;
 
 ELSIF :Report_Type = 'M' THEN
   v_out_file  :=  'C:\USERDATA\'||:CLAIM_NUM||'_Med_'||TO_CHAR(SYSDATE,'MMDDYY')||'.PDF';
   v_Where     := 'AND PTC.BP_MEDICAL = ''Y''' ;
   v_Order_By  := 'PTC.BP_MEDICAL,PTC.BP_REPORT_ORDER,';
   v_Title     :=  '''(Medical Payments Only)''';
 
 ELSIF :Report_TYPE = 'C' THEN
   v_out_file  :=  'C:\USERDATA\'||:CLAIM_NUM||'_Comp_'||TO_CHAR(SYSDATE,'MMDDYY')||'.PDF';
   v_Where     := 'AND PTC.BP_COMP=''Y''' ;
   v_Order_By  := 'PTC.BP_COMP,PTC.BP_REPORT_ORDER,';
   v_Title     :=  '''(Compensation Payments Only)''';
 END IF;

 Param_List_id := Create_Parameter_List ( 'Report_Params');
 Add_Parameter ( Param_List_id, 'P_CLAIMNUM', TEXT_PARAMETER, :CLAIM_NUM);
 Add_Parameter ( Param_List_id, 'P_NAME',     TEXT_PARAMETER, :NAME);
 Add_Parameter ( Param_List_id, 'P_DOI',      TEXT_PARAMETER, TO_CHAR(:DOI,'DD-MON-RR'));
 Add_Parameter ( Param_List_id, 'P_WHERE',    TEXT_PARAMETER, v_Where);
 Add_Parameter ( Param_List_id, 'P_ORDER_BY', TEXT_PARAMETER, v_Order_By);
 Add_Parameter ( Param_List_id, 'P_TITLE',    TEXT_PARAMETER, v_Title);
 Add_Parameter ( Param_List_id, 'P_DB_FILE',  TEXT_PARAMETER, v_DB_File);

--Generate to Screen
IF p_REP_TYPE ='SCRN' THEN
 Add_Parameter ( Param_List_id, 'BACKGROUND', TEXT_PARAMETER, 'NO');
 Add_Parameter ( Param_List_id, 'BATCH',      TEXT_PARAMETER, 'NO');
 Add_Parameter ( Param_List_id, 'PARAMFORM',  TEXT_PARAMETER, 'NO');
 Add_Parameter ( Param_List_id, 'DESTYPE',    TEXT_PARAMETER, 'SCREEN');
 Add_Parameter ( Param_List_id, 'DESFORMAT',  TEXT_PARAMETER, 'dflt');
 Add_Parameter ( Param_List_id, 'DESNAME',    TEXT_PARAMETER, Null);
 Add_Parameter ( Param_List_id, 'DISABLEFILE',TEXT_PARAMETER, 'YES');
 Add_Parameter ( Param_List_id, 'DISABLEMAIL',TEXT_PARAMETER, 'YES');
 Run_Product(REPORTS,'N:\SAL2\BENEFITS_PAID\BENEFITS_PAID_REPORT', ASYNCHRONOUS, RUNTIME, FILESYSTEM , Param_List_id, NULL);
--Run_Product(REPORTS,'C:\BENEFITS_PAID\BENEFITS_PAID_REPORT', ASYNCHRONOUS, RUNTIME, FILESYSTEM , Param_List_id, NULL);
END IF;

--Generate to File
IF p_REP_TYPE = 'FILE' THEN 
 Add_Parameter ( Param_List_id, 'BACKGROUND', TEXT_PARAMETER, 'NO'); -- 'YES' 09/25/03
 Add_Parameter ( Param_List_id, 'BATCH',      TEXT_PARAMETER, 'YES');
 Add_Parameter ( Param_List_id, 'DESTYPE',    TEXT_PARAMETER, 'FILE');
 Add_Parameter ( Param_List_id, 'DESFORMAT',  TEXT_PARAMETER, 'PDF');
 Add_Parameter ( Param_List_id, 'DESNAME',    TEXT_PARAMETER, v_Out_File);
 MESSAGE('Retrieve the requested file at:'||chr(13)||v_out_file);
 message(' ', no_acknowledge);

-- Call to run report --
 Run_Product(REPORTS,'N:\SAL2\BENEFITS_PAID\BENEFITS_PAID_REPORT', ASYNCHRONOUS, RUNTIME, FILESYSTEM , Param_List_id, NULL);  
 END IF;

 

 DESTROY_PARAMETER_LIST(Param_List_id); 
 MESSAGE (' ', no_acknowledge);
 GO_ITEM('EXIT_PB');
 
EXCEPTION
 WHEN OTHERS THEN
  DESTROY_PARAMETER_LIST(Param_List_id); 
  MESSAGE ('Report Generation Failed.', acknowledge);
  MESSAGE (Error_Type||' / '|| Error_Code||' / '||Error_Text);
  RAISE FORM_TRIGGER_FAILURE;
END;