Discussions

Web tier: servlets, JSP, Web frameworks: Passing data to Oracle Report from JSP

  1. Passing data to Oracle Report from JSP (3 messages)

    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
  2. 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
  3. 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
  4. 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;