6 Replies Latest reply on Jan 13, 2018 11:46 PM by Pavel_p

    Passing parameters from APEX to Oracle Reports (URL)

    JakeMorgan

      I'm currently investigating different ways of suporting our legacy Oracle Reports (11g) as the provider of our third-party ERP no longer supports running Oracle Reports from withing their application. 

       

      One method suggested is to use APEX as a front-end, taking the parameters from within APEX via "form"/fields and passing them to an an Oracle Report via a URL string (I believe we can generate reports using variables passed in URL string) .  Is this possible, and how can it be achieved in APEX (5.x)?

        • 1. Re: Passing parameters from APEX to Oracle Reports (URL)
          InoL

          Since calling a report is just a URL, it is easy. Just create the correct URL.

          This a very old example, but it still works like this:

          Application Express: How To Integrate Oracle Reports with Oracle HTML DB

           

          Googling probably gives you more recent examples, like:

          http://rajiboracle.blogspot.com/2014/01/call-oracle-10g-report-form-oracle-apex.html

          • 2. Re: Passing parameters from APEX to Oracle Reports (URL)
            Scott Wesley

            Sure you can, you've always been able to execute Oracle Reports via a URL call, passing the parameters in the standard web format.

             

            Here's a really old reference

            Application Express: How To Integrate Oracle Reports with Oracle HTML DB

             

            I don't have details with me, but you can edit cgicmd.dat to embed the username/password, so it's not visible in the URL.

            • 3. Re: Passing parameters from APEX to Oracle Reports (URL)
              -Max-

              Hello Jake

               

              As mentioned using the URL will work for sure.

              We used a slightly different approach.

               

              We create two table to handle the report generation.

              First table was used to define a call to a report. Second was used to define all parameter/value.

               

              Then we had the database call the generated URL (using UTL_HTTP) and retrieve the blob of the report.

              That blob can then be either downloaded, stored or displayed inline (we have a display mode in the first table to handle that).

               

              In APEX, we use a submit process to create the report record, add the corresponding parameters Then on page load we open a new window using JS that points to a page that calls the rendering function.

               

              We used that approach because some parameters needed to be hidden to the users.

               

              For it to work, you might need to create the corresponding ACL in order for the DB to be able to call the report server.

               

              Regards

              Max

              • 4. Re: Passing parameters from APEX to Oracle Reports (URL)
                InoL

                That is also a good approach.

                Instead of using utl_http, I would use the SRW package (the database package, not to be confused with the Reports Builder SRW package). It has a lot of extra's, like debugging.

                • 5. Re: Passing parameters from APEX to Oracle Reports (URL)
                  Pavel_p

                  Hi,

                  for sure it is possible to invoke the report directly with a simple link, however it has several drawbacks. The report server would have to be exposed to client machines which is a potential security risk and either it can be publicly accessible with no protection, or the username/pwd would have to be a part of URL. You probably do not want your users see these things.

                  If you make the report call server side (as Max suggests), you can handle security quite easily even without password protection - the only one permitted machine could be the DB server. However if you do not have some really special needs, I would suggest to use apex_web_service.make_rest_request_b https://docs.oracle.com/database/apex-5.1/AEAPI/MAKE_REST_REQUEST_B-Function.htm#AEAPI29937 rather than the low-level utl_http package as it does all the "heavy lifting" for you and does not need a special grant (actually it's a wrapper for utl_http).

                  So the code to invoke the report could look like this (untested, so some minor fixes might be needed):

                  declare
                    l_rep_blob  blob;
                  begin
                  /* set request headers if needed
                  apex_web_service.g_headers(1).name := 'request-header-name';
                  apex_web_service.g_headers(1).value := 'request-header-value';
                    */
                  
                  
                    --make the http request
                    l_rep_blob := apex_web_service.make_rest_request_b(
                      p_url => 'http://your_report_url',
                      p_http_method => 'GET');
                  
                    --make sure the call was successful
                    if apex_web_service.g_status_code = 200 then --OK
                      apex_debug.message('DEBUGMSG: report invoked successfully');
                    else
                      apex_debug.message('DEBUGMSG: report request failed with response code %s',apex_web_service.g_status_code);
                      --no need to continue
                      raise_application_error(-20000,'report request failed');
                    end if;
                  
                  --and serve the blob as an attachment
                  
                    htp.init;
                    owa_util.mime_header('application/pdf',false);
                    htp.p('Content-length: ' || dbms_lob.getlength(l_rep_blob) );
                    htp.p('Content-Disposition: attachment; filename="my_report.pdf"');
                    owa_util.http_header_close;
                    wpg_docload.download_file(l_rep_blob);
                    apex_application.stop_apex_engine;
                  end;
                  

                  You'll also need to set ACLs and eventually a Wallet if your report server uses https protocol (more about both here Re: Call API from plsql and get value from JSON response ).

                  Regards,

                  Pavel

                  • 6. Re: Passing parameters from APEX to Oracle Reports (URL)
                    Alastair M

                    You can also avoid having the reports username/password in the url by creating a key map file: https://docs.oracle.com/cd/E16764_01/bi.1111/b32121/pbr_run013.htm

                     

                    If exposing the reports server itself is a concern then you can proxy through to the reports server if you have an Apache server or similar in your environment already.

                     

                    This might be a bit easier than the above suggestions depending on your current set up.