Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

Call BI Publisher Web Services from APEX

Posted by Tyler Muth on March 31, 2008

Integration between Application Express and BI Publisher is primarily focused on delivering high fidelity reports to the browser, such as clicking a “Print” link on an APEX report and getting back a PDF version of that report in your browser. The configuration and architecture of this integration is documented here. However, there have been a number of questions on the APEX forum and from customers I’ve presented to that are not answered by this solution. So, with the knowledge that BI Publisher 10.1.3.3.2 introduced Web Service APIs to run reports (documented here), and Application Express 3.0 introduced the ability to call Web Services, I thought there might be another integration point between these two products.

This technique is completely different from the traditional integration, since the database where the report resides can be completely different from the database where APEX is installed. We’re going to configure a database connection in BI Publisher then build a report using that connection. APEX is essentially going to pass some parameters to that report and doesn’t need access to the data at all. Since we’re defining the data source in BI Publisher, the report data can actually come from any JDBC compliant database (Oracle, SQL Server, MySQL, etc), a Web Service, or even File data sources.

In summary:

  • Select zero or more parameters on an APEX Page
  • Press Submit
  • This calls an APEX Process of type “Web Service Reference”
  • The Web Service Call instructs BI Publisher to run a report with the given parameters
  • Then deliver it to one or more email address and an FTP server (could also be WebDav, Fax, or a Network Printer)

Software used in the post:

Please make sure all of the following software is installed and configured before starting. You can obviously omit either the Email server or the FTP server if you only care about 1 delivery end point. Make sure you test the Email and / or FTP servers with their respective clients before you try delivering reports to them with BI Publisher.

Create a Report in BI Publisher

If you are not familiar with BI Publisher, please take the time to go through this Oracle By Example (OBE). The OBE will walk you through all of the steps necessary to build the report in BI Publisher. Once you are familiar with BIP:

  1. Create a report based on HR.DEPARTMENTS and HR.EMPLOYEES. This report has a parameter that allows you to select which department you want to view and uses an RTF template to format the output.
  2. Configure your SMTP and FTP servers in the Admin interface
  3. Click on the “Schedule” link for the report and test FTP delivery first as its less prone to errors. Then test email delivery. Make sure these work from the schedule interface before proceeding. If they don’t work here, there’s no chance they’ll work when called from a Web Service API.
  4. Make note of the URL of your report as you’ll use this later in the Web Service API.

Here are 2 screen-shots of my report:

report edit

report view

Test The Web Service Call in soapUI

Now that we know we can schedule a report to be delivered via FTP and Email, lets test the BI Publisher Web Service Call to this report. Start by creating a new WSDL Project in soapUI. From the BI Publisher Web Service API Documentation, use the following URL for the WSDL:

http://<host>:<port>/xmlpserver/services/PublicReportService?wsdl

Create a New Request under PublicReportService > scheduleReport. Lets start with a simple request to deliver our report to FTP with no parameters:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Body>
      <pub:scheduleReport xmlns:pub="http://xmlns.oracle.com/oxp/service/PublicReportService">
         <scheduleRequest>
            <deliveryRequest>
               <ftpOption>
                  <ftpServerName>tmuth-ftp</ftpServerName>
                  <ftpUserName>tyler</ftpUserName>
                  <ftpUserPassword>oracle1</ftpUserPassword>
                  <remoteFile>/employees.pdf</remoteFile>
               </ftpOption>
            </deliveryRequest>
            <notificationTo>tyler@tmuth-lap</notificationTo>
            <notifyWhenFailed>true</notifyWhenFailed>
            <reportRequest>
               <attributeFormat>pdf</attributeFormat>
               <reportAbsolutePath>/~tmuth/employees/employees.xdo</reportAbsolutePath>
            </reportRequest>
            <userJobName>employees</userJobName>
         </scheduleRequest>
         <userID>tmuth</userID>
         <password>oracle1</password>
      </pub:scheduleReport>
   </soapenv:Body>
</soapenv:Envelope>

Some things to note about the code above:

  • Line 7: “tmuth-ftp” isn’t the actual server name, it’s the name I gave it in the BIP Admin interface when I added the FTP server.
  • Line 10: Don’t forget the leading slash
  • Line 17: This is the URL I noted when I ran my report. The BIP interface will often escape the tilde with a percent seven E, but you can just use the tilde here.
  • Lines 21 and 22: This is the username and password I used to login to the BIP Web Interface.

Once that is working, you can try adding a parameter to narrow it to just the sales department. Add the following code at the end of the “reportRequest” section between lines 17 and 18:

<parameterNameValues>
  <item>
     <name>DEPARTMENT</name>
     <multiValuesAllowed>false</multiValuesAllowed>
     <values>
        <!--  80 = SALES, -1 = All -->
        <item>80</item>
     </values>
  </item>
</parameterNameValues>

Now, lets add email as a second delivery end-point. Add the following code to the “deliveryRequest” section between lines 11 and 12:

<emailOption>
  <emailBody>webservice test</emailBody>
  <emailFrom>bip@localhost</emailFrom>
  <emailSubject>test</emailSubject>
  <emailTo>tyler@tmuth-lap</emailTo>
</emailOption>

Create an APEX Application to Call The Report

  1. Create a new APEX application with one blank page (Page 1).
  2. Create a new “Web Service Reference” (Application > Shared Components > Logic)
    1. Do not search a UDDI repository.
    2. Create the reference manually (link on the right side of the page).
    3. Enter the WSDL you used for soapUI
    4. Enter the SOAP envelope at the bottom of this section (make sure you change the values to match your configuration).
  3. Create an Item on Page 1 of type “Select List”. The LOV should be a query of department_name,department_id from hr.departments. The NULL value is -1.
  4. Create a Button labeled “Deliver Report”. Also have the page branch to itself.
  5. Create a Process (on-submit):
    1. Type: Web Service
    2. Select the Web Service Reference you created earlier.
    3. Only fire this process when you press the Deliver Report button.
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Body>
      <pub:scheduleReport xmlns:pub="http://xmlns.oracle.com/oxp/service/PublicReportService">
         <scheduleRequest>
            <deliveryRequest>
               <ftpOption>
                  <ftpServerName>tmuth-ftp</ftpServerName>
                  <ftpUserName>tyler</ftpUserName>
                  <ftpUserPassword>oracle1</ftpUserPassword>
                  <remoteFile>/employees.pdf</remoteFile>
               </ftpOption>
               <emailOption>
                  <emailBody>webservice test</emailBody>
                  <emailFrom>bip@localhost</emailFrom>
                  <emailSubject>test</emailSubject>
                  <emailTo>tyler@tmuth-lap</emailTo>
               </emailOption>
            </deliveryRequest>
            <notificationTo>tyler@tmuth-lap</notificationTo>
            <notifyWhenFailed>true</notifyWhenFailed>
            <reportRequest>
               <attributeFormat>pdf</attributeFormat>
               <reportAbsolutePath>/~tmuth/employees/employees.xdo</reportAbsolutePath>
               <parameterNameValues>
                  <item>
                     <name>DEPARTMENT</name>
                     <multiValuesAllowed>false</multiValuesAllowed>
                     <values>
                        <item>#P1_DEPARTMENT#</item>
                     </values>
                  </item>
               </parameterNameValues>
            </reportRequest>
            <userJobName>employees</userJobName>
         </scheduleRequest>
         <userID>tmuth</userID>
         <password>oracle1</password>
      </pub:scheduleReport>
   </soapenv:Body>
</soapenv:Envelope>

Notice that we reference APEX Items in Web Service Requests using #ITEM# notation.You can now test your application by selecting a department then submitting the page.apex_app.png

Use Cases

  • Long running reports, asynchronously delivered via email
  • Print bar codes via network attached printer
  • Publishing a report in HTML or PDF format to a web site via FTP

Downloads

Click here to download the APEX Application, BI Publisher Report, and PDF output of the BIP Report. Note that in the APEX application, the P1_DEPARTMENT item queries HR.DEPARTMENTS. employees.zip is the BIP Report which you simply want to upload to BI Publisher “as-is” without unzipping.

About these ads

46 Responses to “Call BI Publisher Web Services from APEX”

  1. Very nice post Tyler! Well done.

    Dimitri

  2. […] Check out Tyler’s post on using BIP web services to integrated BIP and Application Express: Call BI Publisher Web Services from APEX 3/31/08; 10:40:28 PM Comment [0] Mortgage Rate LiesTim Dexter Not so much a how to today – that […]

  3. Really helpful, thank you for this great post Tyler.

  4. Jason said

    Tyler,

    Very nice, and a very lucid example of using the Manual Web references feature of Application Express (one of my favorites). One thing I would note is that in your instructions for creating the Manual Web reference, the URL under Service Description should be the address of the service, and not the URL of the WSDL itself. This address can be found by inspecting the WSDL and looking for /definitions/service/port/soap:address/@location.

    Regards,

    Jason

  5. Todd said

    Tyler,

    Thanks for taking the time to create this nice post. This logic will really benefit applications that need to submit reports to run in the background (long running reports…).

    Thank You!
    Todd

  6. Partha said

    Thanks for the nice article. There has been a lot of emphasis on BI Publisher and how it is a preferred solution for nice formatted reports for APEX, however, it is not mentioned whether BI Publisher standalone is an extra cost option or the licensing model. With the complexity of the licensing model of Oracle, it becomes difficult to judge this. Also, since APEX comes with the database, and because of the embedded gateway with 11g, there is no need for Oracle Application server, we would like to have options for no-more-license solution for the reports as well.

    Second comment is the username / password in most of the examples. Is this the only way to do it? where the usernames and passwords have to necessarily be hardcoded the way it is given in the example? The solution (integration) mentioned above is quite new for people who are not used to java or xml or webservices, so, they try to do copy / paste and customize for their requirements, but then, when it goes to production, the DBA has a say on the production passwords and corporate policy dictates that dba changes the passwords every month – this will lead to application dependancy; Hence if there are alternate ways to do this, kindly mention them in future articles or use them as default even if it means a level of complexity.

    Partha K

  7. Tyler Muth said

    Partha,

    BI Publisher requires a license, as do almost all other Oracle products. If Oracle only charged for the Database, the company would collapse and a lot of stock holders would not be happy. APEX is a bit of an anomaly in Oracle as its one of the few free products. As far as the embedded gateway, its nice for development, but I would never choose it for a production system over Oracle HTTP Sever based on Apache. Apache has a very long positive track-record, a lot of configuration options and modules, and a large number of people in the IT community who know it well.

    As far as the username / password issue, this is the only way I know of, but there might be another way. There is the concept of running a report as “guest” in BIP, but I don’t know if that’s an option in the web service APIs. I’ll see if one of the BI guys can chime-in. You might want to ask it on the BIP forum as well. I certainly see your point here.

  8. Tony Miller said

    Tyler,
    Awesome job on the article.. This will help with mey new job here in Houston. We have batch run reports that take a while right now IN Oracle Reports and driven by a forms based application. Trying to help move stuff to APEX and BI Publisher..

    Thanks !

    Tony Miller
    Webster, TX

  9. […] on a bit of luck with the setting up of BI Publisher Web Service access for ApEx as I came across this excellent article by Tyler Muth that documents the whole process. In the article, Tyler uses a tool called soapUI to […]

  10. […] If you had worked on APEX and BI Publisher integration (using Web Services) as documented here excellently by Tyler Muth you would have noticed that APEX can identify BI Publisher web services […]

  11. Pooja said

    Hi Tyler,

    Am very much new to web services technology…. I need a help in using the web services for running the BI Publisher report through backend and its very urgent for me so as to deliver this to my client. We had followed the link mentioned above “BI Publisher Web Service API Documentation” for generating the required class files but we are running out with errors. Could you please share a sample working report with exact version of the jar files used and commands to be executed in sequence.

    Thanks for your help in advance…

    Pooja

  12. Tyler Muth said

    Pooja,

    Sorry, there are no jar files or Java involved whatsoever. Consider this example decaffeinated. Are you trying to call the BIP Web Services from Application Express?

  13. Pooja said

    Hi Tyler,

    Thanks for a quick reply.. basically my requirement is to call the bip webservice through a Java Program(which is inturn registered as an iBot in OBIEE Applications). Can you please suggest any way to call the BIP report in an iBot.

    Thank You

  14. Yesh Nandi said

    Tyler:

    Great article. Could you give an example of how the SOAP response would be collected back if I need to run the report in APEX itself (rather than your example of ftp and email.)

    I am a little confused about the SOAP response structure of a multi-page report (PDF or HTML ) …

    Thanks – Yesh

  15. Yesh Nandi said

    Hi Tyler:

    When I execute the “runreport” operation in SOAPUI, I get a SOAP response with

    JVBERi0xLjQNCjYgMCBvYmoNCjw8IC9MZW5ndGggMTg3NSAvRmlsdGVyIC9GbGF0ZURlY2>

    application/pdf

    How can I collect this information and display it in my APEX application ?

    Thanks – Yesh

  16. hari said

    helpful article,
    but how about throw BI Publiser sessionid to Apex?

  17. David said

    Just a question related to this. How do you deal with the situation in which reports security is an issue (ie. were not willing to publish our reports wide open to the public user)? Were running in an OID SSO environment so if the visitor went to the report directly their authentication would pass but how would you go about passing the authentication information where APEX is an SSO application and BIP is SSO enabled and the report is not publicly accessible.

  18. name said

    Hello!,

  19. Madmax said

    When I execute the “runreport” operation in SOAPUI, I get a SOAP response with

    JVBERi0xLjQNCjYgMCBvYmoNCjw8IC9MZW5ndGggMTg3NSAvRmlsdGVyIC9GbGF0ZURlY2>

    application/pdf

    The response is base64 encoded. You’ll have to decode it back into binary before it can be used as a PDF.

  20. CBASHYB said

    How do we burst using the SOAP call? It seems that the delivery request overides anything in the xdo file

  21. Yesh said

    Do you have any examples of how to decode from base64 to binary ?

  22. Ahcene said

    Hello,

    I installed the sample Integrating APEX with BI Publisher based on flex_ws_api package – http://www.oracle.com/technology/products/database/application_express/pdf/integrating_oracle_application_express_with_bi_publisher.pdf
    I created a BI Publisher’s user with no roles assigned.

    The list of folders appear correctly with the user connected to BIP. however no displayed BI Publisher folder when the same user is connected to sample Integrating APEX with BI Publisher.
    The list of folders is empty.

    Also, the APEX’s sample application displays the following message “ORA-31167: XML nodes over 64K in size cannot be inserted”
    for some reports.

    configuration : Oracle 10gXE – APEX 3.2 – BI Publisher 10.1.3.4.1 – MS Vista

    what do I do

    Thanks for your help

    Ahcene

    • Alex said

      I get the same -31167 error message which comes about from the final call in the flex_ws_api.make_request function: “return xmltype.createxml(l_clob);” Is there another way of returning the XML rather than xmltype.createxml()?

      Alex.

  23. Jeremy Tittle said

    Great post, love the detail… came across this while looking at the apex web service sample that points back another of your blog post to fetch the flex_ws_api procedure.

    How does that one (which seems more complex) differ from this one? The end resulted UI is different but they are basically doing the same thing right?

    One other question, which might come off as a stupid one but I have to ask, why wouldn’t the user just go and load up BI EE directly to get their report? Not saying this isn’t useful, once our local APEX instance get’s back up I’m playing on trying this out, but it’s a question I have to ask. I have enough trouble getting users to use the tools infront of them, let alone use another tool that’s going to get them to the same place.

    Thanks,
    Jeremy

  24. Jeremy Tittle said

    Oops left off the closing A tag in my post, please edit if you get a chance.

    Jeremy

  25. Very useful information. Can u give more information to extract emails from facebook also…

  26. Suresh said

    Great article,but how to integrate this with Oracle Forms(9i/10g) application ? please advise.

    Thanks,
    Suresh

  27. xnnc said

    @Suresh : move to apex, Forms is way to old ;) (sorry it wasn’t the answer you wanted)

    @Tyler : You are my hero, we were doing quite the same but using a PL/SQL to generate the soap and then calling the WS of BI, but too long and difficult to understand, and a lot of code for nothing in fact.
    Thank you

  28. cstmic said

    Hi,
    i can’t download your application from
    [http://drop.io/apex_bip_webservices]

    Any help?
    Thanks in Advance
    cm

  29. cstmic said

    Hi Tyler,
    i can’t download your application from
    [http://drop.io/apex_bip_webservices]

    Any help?
    Thanks in Advance
    cm

  30. useful Bi info

  31. Alexander said

    Didn’t work on 11.1.1.5.0 :(

    org.xml.sax.SAXException: Invalid element in oracle.xdo.webservice.ScheduleRequest – deliveryRequest

    • Garry Lawton said

      Hi,
      it should work with 11.1.1.5.0 using this service: http://<host&gt;:<port&gt/xmlpserver/services/v2/ScheduleService?wsdl but I am encountering a nullPointerException with FTP – a bug? most probably.
      It does however work with WebDAV which I think is a good alternative.
      Here is an example soap envelope with both FTP and WebDAV:

      
         
         
            
               
                  
                     
                        
                           ftphost
                           ftpusername
                           ftppassword
                           /report1.htm
                        
                     
                     
                        
                           Basic
                           webdavpassword
                           /report1.htm
                           webdavhost
                           webdavusername
                        
                     
                  
                  me@gmail.com
                  true
                  false
                  false
                  
                     
                        
                           
                              false
                              DEPARTMENT
                              
                                 #P1_DEPARTMENT#
                              
                           
                        
                     
                     html
                     REPORT1_TEMPLATE
                     /DBX_GUEST/REPORT1.xdo
                  
                  WebDAVJob
               
               BIusername
               BIpassword
            
         
      
      

      Regards Garry Lawton

    • Garry Lawton said

      Hi,
      it should work with 11.1.1.5.0 using this service: http://<host&gt;:<port>/xmlpserver/services/v2/ScheduleService?wsdl but I am encountering a nullPointerException with FTP – a bug? most probably.
      It does however work with WebDAV which I think is a good alternative.
      Here is an example soap envelope with both FTP and WebDAV:

      ;
      
      <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:v2="http://xmlns.oracle.com/oxp/service/v2">
         <soapenv:Header/>
         <soapenv:Body>
            <v2:scheduleReport>
               <v2:scheduleRequest>
                  <v2:deliveryChannels>
                     <v2:ftpOptions>
                        <v2:item>
                           <v2:ftpServerName>ftphost</v2:ftpServerName>
                           <v2:ftpUserName>ftpusername</v2:ftpUserName>
                           <v2:ftpUserPassword>ftppassword</v2:ftpUserPassword>
                           <v2:remoteFile>/report1.htm</v2:remoteFile>
                        </v2:item>
                     </v2:ftpOptions>
                     <v2:webDAVOptions>
                        <v2:item>
                           <v2:deliveryAuthType>Basic</v2:deliveryAuthType>
                           <v2:password>webdavpassword</v2:password>
                           <v2:remoteFilePath>/report1.htm</v2:remoteFilePath>
                           <v2:server>webdavhost</v2:server>
                           <v2:userName>webdavusername</v2:userName>
                        </v2:item>
                     </v2:webDAVOptions>
                  </v2:deliveryChannels>
                  <v2:notificationTo>me@gmail.com</v2:notificationTo>
                  <v2:notifyWhenFailed>true</v2:notifyWhenFailed>
                  <v2:notifyWhenSuccess>false</v2:notifyWhenSuccess>
                  <v2:notifyWhenWarning>false</v2:notifyWhenWarning>
                  <v2:reportRequest>
                     <v2:parameterNameValues>
                        <v2:listOfParamNameValues>
                           <v2:item>
                              <v2:multiValuesAllowed>false</v2:multiValuesAllowed>
                              <v2:name>DEPARTMENT</v2:name>
                              <v2:values>
                                 <v2:item>#P1_DEPARTMENT#</v2:item>
                              </v2:values>
                           </v2:item>
                        </v2:listOfParamNameValues>
                     </v2:parameterNameValues>
                     <v2:attributeFormat>html</v2:attributeFormat>
                     <v2:attributeTemplate>REPORT1_TEMPLATE</v2:attributeTemplate>
                     <v2:reportAbsolutePath>/DBX_GUEST/REPORT1.xdo</v2:reportAbsolutePath>
                  </v2:reportRequest>
                  <v2:userJobName>WebDAVJob</v2:userJobName>
               </v2:scheduleRequest>
               <v2:userID>BIusername</v2:userID>
               <v2:password>BIpassword</v2:password>
            </v2:scheduleReport>
         </soapenv:Body>
      </soapenv:Envelope>
      
      

      Regards Garry Lawton

  32. Garry Lawton said

    Tyler

    by the way – great article

    Regards Garry

  33. Latina Woman…

    […]Call BI Publisher Web Services from APEX « Tyler Muth’s Blog[…]…

  34. Toby said

    Link exchange is nothing else except it is only placing the other person’s website link on your page at appropriate place and other person will also do same for you.

  35. Fascinating blog! Is your theme custom made or did you download it from somewhere?
    A theme like yours with a few simple tweeks would really
    make my blog stand out. Please let me know where you got your design.
    Kudos

  36. Hey! I realize this is somewhat off-topic however I needed to ask.
    Does operating a well-established blog like yours require a large amount
    of work? I am brand new to operating a blog but I do write in my journal everyday.
    I’d like to start a blog so I will be able to share my personal experience and thoughts online. Please let me know if you have any kind of suggestions or tips for new aspiring bloggers. Appreciate it!

  37. Magnificent goods from you, man. I’ve understand your stuff previous to and you are just extremely fantastic. I really like what you’ve acquired here, really like what you’re stating and the way in which you say it. You make it enjoyable and you still take care of to keep it smart. I cant wait to read far more from you. This is actually a great website.

  38. A Microphone Fiend. I was moved enough to drop a comment :) I actually do have 2 questions for you if
    you tend not to mind. Is it just me or do a few of these remarks look like coming from brain dead folks?
    :-P And, if you are posting on other social sites, I would like to
    keep up with
    you. Could you list
    the complete urls of your shared pages like
    your linkedin profile,
    Facebook page or twitter feed?

  39. Howdy! I know this is kinda off topic but I’d figured I’d ask.
    Would you be interested in trading links or maybe guest writing a blog article or
    vice-versa? My website addresses a lot of the same topics as yours and I think we could greatly
    benefit from each other. If you happen to be interested feel free to
    shoot me an e-mail. I look forward to hearing from you!

    Great blog by the way!

  40. Wow, awesome blog format! How long have you ever been
    blogging for? you made blogging glance easy. The whole look of
    your site is great, as neatly as the content material!

  41. Have you ever considered writing an e-book or guest authoring on other websites?
    I have a blog based upon on the same information you discuss and would really like to have you share
    some stories/information. I know my subscribers would appreciate your work.
    If you are even remotely interested, feel free to send me an e mail.

  42. order flowers now

    Call BI Publisher Web Services from APEX « Tyler Muth’s Blog

  43. Best SEO in Maryland

    Call BI Publisher Web Services from APEX « Tyler Muth’s Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 93 other followers

%d bloggers like this: