Solved

WebForms calling WebReports

Posted on 2004-10-27
4,593 Views
Last Modified: 2007-12-19
Hello,

I have read that system parameters cannot be passed to a parameter list when Forms call Reports over the Web in Oracle 9i.
After using the FMA to migrate my code from ORacle 6i to 9i, I made the following modificaitons to allow me to print my reports over the WEb to my Network Printer installed on my Linux server.

HEre is the sample code:
-----
         ---repid := find_report_object('btprtinv');
            ---add_parameter(pl_id,'PARAMFORM',TEXT_PARAMETER,'YES');
            --System Parameters not part of Parameter List
            --add_parameter(pl_id,'DESTYPE',TEXT_PARAMETER,'PRINTER');
            --add_parameter(pl_id,'DESTYPE',TEXT_PARAMETER,'PRINTER');
            ---add_parameter(pl_id,'COPIES',TEXT_PARAMETER,'2');
            --add_parameter(pl_id,'DESNAME',TEXT_PARAMETER,'Lexmark Upper Tray');
            --add_parameter(pl_id,'DESNAME',TEXT_PARAMETER,'lxut');
         ---set_report_object_property(repid, report_destype, printer);
         ---set_report_object_property(repid, report_desname, 'lexmark lower tray');

        ---set_report_object_property(PARAMETER.rp2rrodestype,report_destype,printer);
        ---set_report_object_property(PARAMETER.rp2rrodesname,report_desname,'lexmark lower tray');

            ---add_parameter(pl_id,'P_PRINT_DATE',TEXT_PARAMETER,to_char(:variable.print_date));
            ---add_parameter(pl_id,'P_PROJECT',TEXT_PARAMETER,:variable.project);
            ---add_parameter(pl_id,'P_CO',TEXT_PARAMETER,:company.co);
            ---add_parameter(pl_id,'P_SURVEYOR',TEXT_PARAMETER,to_char(:variable.surveyor));
            
            ---rp2rro.rp2rro_run_product(REPORTS,'btprtinv',SYNCHRONOUS,RUNTIME,FILESYSTEM,pl_id,null);
         
         repid := find_report_object('btprtinv');
         set_report_object_property(repid, report_destype, printer);
         set_report_object_property(repid, report_desname, 'lxut');
    set_report_object_property(repid, report_other, 'paramform=yes ' || 'copies=2' ||
                               'P_PRINT_DATE=' || to_char(:variable.print_date) ||
                               'P_PROJECT=' || :variable.project ||
                               'P_CO=' || :company.co ||
                               'P_SURVEYOR=' || to_char(:variable.surveyor));
            
    v_rep := run_report_object(repid);
    rep_status := report_object_status(v_rep);
         
     while rep_status in ('RUNNING', 'OPENING_REPORT', 'ENQUEUED') loop
        rep_status := report_object_status(v_rep);
     end loop;

     if rep_status = 'FINISHED' then
        -- display the report in the browser
        rep_url := 'http://Linux2005:7780/reports/rwservlet/getjobid' ||
                   substr(v_rep, instr(v_rep, '_', -1) +1) ||
                   '?'||'server=rep_Linux2005';
        web.show_document(rep_url, '_blank');
     else
        message('Error when running the report', 1);
     end if;
            
  end if;

  destroy_parameter_list(pl_id);
-----
I cannot compile the instructions report_object_status.
1) Any ideas why ?

This is how my variables are initialised:

    pl_id      PARAMLIST;
    repid report_object;
    v_rep VARCHAR2(100);
    rep_status VARCHAR2(100);
    rep_url varchar2(500);

2) I cannot invoke/run my report in my Oracle 9i iDS.
I had noticed that I was making referenc yo a reports server called RepSRV which I launched.
I check that my report called btprtinv.rep exists.

3) How can I go about resolving this issue ?

PT
0
Question by:ptreves
    9 Comments
     
    LVL 22

    Expert Comment

    by:Henka
    I think that this line ought to be
    v_rep := run_report_object(repid,pl_id);
    instead of
    v_rep := run_report_object(repid);

    You can also increase v_rep and rep_status to VARCHAR2(200);
    0
     

    Author Comment

    by:ptreves
    Hello,

    Could the problem be related to the fact that maybe v_rep is NULL and not referencing the proper Report node ?

    PT
    0
     
    LVL 22

    Expert Comment

    by:Henka
    Yes, of course.
    0
     

    Author Comment

    by:ptreves
    Hello,

    I have been working on the Forms invoking Reports over the Web part of my application.
    I set the system paramters DESTYPE and DESNAME with the set_system as follows before setting variables through the parameter list:
    -----
    /*******************************************
       Code modified by the Forms Migration Assistant
       12-Sep-2004 03:07 PM
     *******************************************/
    PROCEDURE Print_transaction IS
       pl_id PARAMLIST;
       repid report_object;
        v_rep VARCHAR2(200);
        rep_status VARCHAR2(200);
        rep_url varchar2(500);
    BEGIN
     pl_id := Get_parameter_list('TRANSACTION');
     if not id_null(pl_id)
     then destroy_parameter_list(pl_id);
     end if;
     ---'repid := find_report_object('btreppro');
     repid := find_report_object('BTPRTINV');
     pl_id := Create_parameter_list('TRANSACTION');
        set_report_object_property(repid, report_comm_mode, synchronous);
        set_report_object_property(repid, report_execution_mode, batch);
        set_report_object_property(repid, report_destype, cache);
        set_report_object_property(repid, report_desname, 'PDF');
        --set_report_object_property(repid, report_desname, 'Lexmark Upper Tray');
        set_report_object_property(repid, report_server, 'RepSRV');
        ---set_report_object_property(repid, report_other, 'paramform=yes ' || 'copies=2' ||
        ---                           'P_PRINT_DATE=' || to_char(:variable.print_date) ||
        ---                           'P_PROJECT=' || :variable.project ||
        ---                           'P_CO=' || :company.co ||
        ---                           'P_SURVEYOR=' || to_char(:variable.surveyor));
     add_parameter(pl_id,'PARAMFORM',TEXT_PARAMETER,'NO');
     --add_parameter(pl_id,'DESTYPE',TEXT_PARAMETER,'printer');
     --add_parameter(pl_id,'DESNAME',TEXT_PARAMETER,'Lexmark Upper Tray');
     add_parameter(pl_id,'COPIES',TEXT_PARAMETER,'1');
     ----add_parameter(pl_id,'P_PRINT_DATE',TEXT_PARAMETER,to_char(:variable.print_date));
     ----add_parameter(pl_id,'P_PROJECT',TEXT_PARAMETER,:variable.project);
     ----add_parameter(pl_id,'P_CO',TEXT_PARAMETER,:company.co);
    -- add_parameter(pl_id,'P_SURVEYOR',TEXT_PARAMETER,nvl(to_char(:variable.surveyor),'0'));
     --- rp2rro.rp2rro_run_product(REPORTS,'btreppro.rep',SYNCHRONOUS,RUNTIME,FILESYSTEM,pl_id,null);
     
        message('Ok, Parameter List + Report Object Set', 1);
        v_rep := run_report_object(repid, pl_id);
        if v_rep is not null then
         rep_status := report_object_status(v_rep);
             
          message('Report Status Loop....Wait....', 1);
          while rep_status in ('RUNNING', 'OPENING_REPORT', 'ENQUEUED') loop
            rep_status := report_object_status(v_rep);
          end loop;
          message('Ok, Trying to run over the Web', 1);
          if rep_status = 'FINISHED' then
            -- display the report in the browser
            rep_url := 'http://terrain2004:8888/reports/rwservlet/getjobid' ||
                       substr(v_rep, instr(v_rep, '_', -1) +1) ||
                       '?'||'server=RepSRV';
            web.show_document(rep_url, '_blank');
          elsif rep_status = 'TERMINATED_WITH_ERROR' then
            message('TERMINATED_WITH_ERROR, Error when running the report', 1);
          else
            message('Some other Error when running the report', 1);
          end if;
        else
          message('Report object not valid', 1);
        end if;
     destroy_parameter_list(pl_id);
    END;

    =====

    When I run this code, for some reason the run_report_object() and the report_object_status() function do not return proper values.

    I checked under the report node, I have 2 nodes: RP2RRO and BTPRTINV.

    1) From those 2 nodes, can I call 10 different reports ?
    (Shouldn't each different report have a seperate node to be referenced ?)

    Of the 10 reports to be called, 2 make reference/invoke a parameter form.
    2) Are there additional settings to be made for the parameter form ?

    Also, when attempting to debug my code, i noticed that the v_rep variable took the value of RepSRV_0 when I had explicitly set it to RepSRV in the source code.
    3) Could this be a source of problem ?

    PT

    0
     
    LVL 22

    Expert Comment

    by:Henka
    You can use only one report object for each report. Here is my code (it is OUR_DUMMY_REPORT):

    PROCEDURE SPUSTI_REPORT IS
     pl_id           ParamList;
     pl_name         VARCHAR2(10) := 'tempdata';
     report_id       Report_Object;
     report_job_id   VARCHAR2(200);
     rep_status      VARCHAR2(200);
     alert_button    PLS_INTEGER;
     al_id               Alert:= Find_Alert('chyba');
     vc_dummy_report Varchar2(20):='OUR_DUMMY_REPORT';
     L_REPORT                ZOSTAVY.REPORT%TYPE;
     L_REPORT_DESFORMAT      ZOSTAVY.REPORT_DESFORMAT%TYPE;
     L_REPORT_SERVER         ZOSTAVY.REPORT_SERVER%TYPE;
     L_REPORT_SERVER_PATH    ZOSTAVY.REPORT_SERVER_PATH%TYPE;

    BEGIN
     pl_id := Get_Parameter_List(pl_name);
     IF NOT Id_Null(pl_id) THEN
        Destroy_Parameter_List( pl_id );
     END IF;
     pl_id := Create_Parameter_List(pl_name);
     IF Id_Null(pl_id) THEN
        Set_Alert_Property(al_id,alert_message_text,'Nepodarilo sa vytvoriť súbor parametrov potrebný pre spustenie zostavy.Končím spúšťanie zostavy.' );
        alert_button := Show_Alert(al_id);
        RAISE Form_Trigger_Failure;
     END IF;

    ....
      Add_Parameter(pl_id, 'PARAMFORM', TEXT_PARAMETER, 'NO');

      report_id:= FIND_REPORT_OBJECT(vc_dummy_report);

      BEGIN
       Select z.REPORT,z.REPORT_DESFORMAT,z.REPORT_SERVER,z.REPORT_SERVER_PATH
       INTO L_REPORT,L_REPORT_DESFORMAT,L_REPORT_SERVER,L_REPORT_SERVER_PATH FROM ZOSTAVY z WHERE z.id=:Z3_ZOSTAVY_UKAZ.ID;
      EXCEPTION
       WHEN NO_DATA_FOUND THEN
         Set_Alert_Property(al_id,alert_message_text,'ZOSTAVU sa nepodarilo vytvoriť.');
         alert_button := Show_Alert(al_id);
         return;
      END;

      SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_FILENAME,L_REPORT); -- report ZOZNAM_ZAKAZNIKOV
      SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_COMM_MODE,SYNCHRONOUS);

      IF Get_Application_Property(USER_INTERFACE)='WEB' THEN
         SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_EXECUTION_MODE,BATCH);
         SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESTYPE,CACHE);
         SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESFORMAT,L_REPORT_DESFORMAT);  --'html'
         SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_SERVER,L_REPORT_SERVER);     --'RepBA'

         report_job_id := RUN_REPORT_OBJECT(report_id,pl_id);

         rep_status:=REPORT_OBJECT_STATUS(report_job_id);

         WHILE rep_status in ('RUNNING','OPENING_REPORT','ENQUEUED')
         LOOP
             rep_status := report_object_status(report_job_id);
         END LOOP;
         IF rep_status='FINISHED' THEN
            Web.Show_Document(L_REPORT_SERVER_PATH||'/dev60cgi/rwcgi60.exe/getjobid='||report_job_id||'?server='||L_REPORT_SERVER);
         ELSE
            Set_Alert_Property(al_id,alert_message_text,'ZOSTAVU sa nepodarilo vytvoriť.');
            alert_button := Show_Alert(al_id);
         END IF;
       ELSE
         SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_EXECUTION_MODE,RUNTIME);
         SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESTYPE,PREVIEW);
         Add_Parameter(pl_id,'BLANKPAGES', TEXT_PARAMETER, 'NO');
         Add_Parameter(pl_id,'MAXIMIZE', TEXT_PARAMETER, 'YES');

         report_job_id := RUN_REPORT_OBJECT(report_id,pl_id);
       END IF;
    END SPUSTI_REPORT;

    Maybe the problem is in your report objects RP2RRO and BTPRTINV. What is the definition of these objects ?

    The properties of my OUR_DUMMY_REPORT are:
    Name = OUR_DUMMY_REPORT
    Execution Mode = Batch
    Communication Mode = Synchronous
    Report Destination Type = File
    0
     

    Author Comment

    by:ptreves
    Hello,

    In my case, I have a Parameter Form to generate with my Report. Before I generate my report I have to display a Parameter form. This involves defining 3 extra user parameters in my Report: P_ACTION, P_USER_CONNECT and P_SERVERNAME.

    With each report I also have about 4 or 5 user parameters to pass.

    1) Do you have a good example of this ?

    I understand from the oracle documentation that I have to modify my code on the Forms side and then on the Reports side. On the report side, I have to enter some code to generate the HTML ACTION attibute in the BEFORE PARAMETER FORM trigger.

    2) Your toughts ....

    PT
    0
     
    LVL 22

    Expert Comment

    by:Henka
    I have no experiences with generating the HTML ACTION attibute, so I cannot advice.
    0
     

    Author Comment

    by:ptreves
    Hello,

    Any idea where I could get some more information about this ?

    PT


    0
     
    LVL 22

    Accepted Solution

    by:
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    933 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now