Open report page (part 5 of 5)

Demo: https://apex.oracle.com/pls/apex/f?p=80981:3

After posting the SOAP message to the database, we are waiting for the response. The response from the Report Server is wrapped as a <![CDATA[result]]> tag in the SOAP response. If the report has run successfully, this may be an example of the output:

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <SOAP-ENV:Body>
      <ns1:runJobResponse SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xmlns:ns1="urn:oracle-reports-rwclient-RWWebService">
         <return xsi:type="xsd:string"><![CDATA[<?xml version = '1.0' encoding = 'ISO-8859-1' standalone = 'yes'?>
<serverQueues>
   <job id="1666" queueType="past">
      <name>hsd0500r</name>
      <type>report</type>
      <status code="4">Finished successfully </status>
      <owner>RWUser</owner>
      <server>rep_www_asfr10g102</server>
      <destination>
         <desType>CACHE</desType>
         <desFormat>pdf</desFormat>
         <file>14435653.pdf</file>
      </destination>
      <timingInfo>
         <queued>Mar 2, 2019 2:58:59 PM</queued>
         <started>Mar 2, 2019 2:58:59 PM</started>
         <finished>Mar 2, 2019 2:59:00 PM</finished>
      </timingInfo>
   </job>
</serverQueues>]]></return>
      </ns1:runJobResponse>
   </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

First, we filter the CDATA result as a return value (see line 11) of the RUN_REPORT_SOAP procedure:

  -- the actual response is the child of the "soap:Body" element
  l_response_clob := l_response_xml.extract('/soap:Envelope/soap:Body/*'
                     , 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"').getClobVal;

  -- now process ns1 content with CDATA content
  l_response_xml:= XMLType(l_response_clob);

  if l_response_xml.existsnode('//return/text()') = 1
  then
    l_response_clob := convert_html(l_response_xml.extract('//return/text()').getClobVal);
    p_response_xml  := XMLType(l_response_clob);
  end if;

And in the RUN_REPORT procedure we process this XML result and seek for the JOBID (see line 3) or in case of errors (see line 13), the error message:

    if not l_error and regexp_like(l_paramlist,'CACHE','i')
    then
      select extract(l_response_xml, 'serverQueues/job/@id').getNumberVal()
      into   l_jobid
      from   dual
      ;
      if l_jobid is not null
      then
        l_rwservlet_url1 := substr(gb_reports_server, 1, instr(gb_reports_server, '?') - 1)          ;
        l_rwservlet_url2 := substr(gb_reports_server, instr(gb_reports_server, '?') )                ;
        p_url            := l_rwservlet_url1 || '/getjobid' || l_jobid || l_rwservlet_url2           ;
      else
        select extract(l_response_xml, 'serverQueues/error/@message').getStringVal()
        into   l_error_message
        from   dual
        ;
        l_error            := true;
        if l_error_message is null
        then
          l_error_message  := 'An error occurred. Check your input parameters or contact administator';
        end if;
      end if;
    end if; -- error during run_report

When we have found a JOBID we can compose the URL (see line 9 – 11 here above) for opening the output result in a new window. We pass the URL back to Apex (line 14) where the Ajax Callback funtion P3_RunReport was waiting for us:

   apexdemo.run_report
   ( p_json_text          => l_json_clob
   , p_url                => l_url
   , p_error              => l_error
   , p_error_message      => l_message
   , p_debug              => l_debug
   ) ;
     
   --Write some JSON out for the response
   apex_json.open_object();
   if not l_error
   then
     apex_json.write('success', true);
     apex_json.write('url', l_url);
     
     -- write session state variable
     :P3_URL := l_url;
     
     -- DONT write this line !!
     -- apex_json.write('error', 'N'); 
   else
     apex_json.write('success', false);
     apex_json.write('error', l_message);
   end if;
   apex_json.close_object();

And as a final step in the JavaScript promise who initiated the whole process, we open a new window (see line 15):

    apex.server.process(
      "P3_RunReportJSON", { 
          p_clob_01: JSON.stringify(jsonArray),
          pageItems: "P3_URL"
      } ,
      { success: function(data) {
                   apex.debug.log('Success of server process RunReportJSON',data.url);

                   apex.item('P3_URL').setValue(data.url);
                   apex.item("P3_Submit").enable();
            
                   if (data.url) {
                     apex.debug.log('URL contains a value');
                     apex.message.showPageSuccess( "Report has been run successfully. Check new Window." );
                     window.open(data.url,"Report Output", "_blank");  
                   } 
                   else {
                     apex.message.showPageSuccess( "Report has been run successfully!" );
                   }
                 } 
      , error: function(request, status, error) {
                   apex.debug.log(status, error);
                   apex.debug.log(request.responseText);
                   apex.item("P3_Submit").enable(); 
                   apex.message.showErrors([
                     { type: "error",
                       location: "page",
                       message: error,
                       unsafe: false
                     }
                   ]);
                 }
      , loadingIndicatorPosition: "page"  
      }
    ); 

Hopefully you were still with me till the end and enjoyed reading these posts about my Apex Report Server integration. Even better is when you are actually able to use it your environment and make your Apex users happy for a while to run their reports.

The Apex Demo application and the apexdemo package can be downloaded at https://github.com/covalsoest/apex_reports

<< Prev: Submit report page