Forum Stats

  • 3,872,915 Users
  • 2,266,490 Discussions
  • 7,911,382 Comments

Discussions

How to Call BI Publisher Report from Oracle Apex

Hi,

Is there way to Call BI Publisher Report from Oracle Apex. I have BI report in Oracle SaaS, how to call in BI publisher report from Oracle Apex.

Pls. find version detail below


Tagged:

Answers

  • Sylvain Martel
    Sylvain Martel Member Posts: 856 Silver Trophy

    Hello Kannan,

    If you BI report can be called from a web service, then you can from APEX!

    Look at APEX_WEB_SERVICE APIs.

  • Kannan Ranganathan
    Kannan Ranganathan Member Posts: 47 Red Ribbon

    Hi Sylvain,

    I want to get the data from the report and store in table(i.e. getReportData from BI publisher). How to do it? Is there any example for this.

    when I am trying simple addition script mentioned in below URL , I am getting

    ORA-20987: The requested URL has been prohibited. Contact your administrator.

    https://oracle-base.com/articles/misc/apex_web_service-consuming-soap-and-rest-web-services#soap

  • Kannan Ranganathan
    Kannan Ranganathan Member Posts: 47 Red Ribbon

    Hi Sylavin,

    Seems above error due to not running n/w ACL, but when I tried to run N/W ACL, I am getting attached error. Kindly help. I using web apex.


  • Sylvain Martel
    Sylvain Martel Member Posts: 856 Silver Trophy

    Kannan,

    What is  N/W ACL?

    ACL entries cannot be performed with your parsing schema user in APEX, you need to connect as the SYS user.

    Also, if you want to access web services using HTTPS, you will need to create an Oracle wallet to hold the trusted certificates. I imagine your BI Publisher web service is secure.

    The link that you provided has all that information for you.

  • Kannan Ranganathan
    Kannan Ranganathan Member Posts: 47 Red Ribbon

    Hi Sylavin,

    Now I am not trying BIP server one, just adding two numbers web services given by oracle in the above link. My login is having admin previleges. Do I need to create SYS user and run above ACL query ?

  • Sylvain Martel
    Sylvain Martel Member Posts: 856 Silver Trophy

    I'm sorry but I'm not sure I understand what you are trying to tell me in the above post.

    Just to recap...

    • You have a BI Publisher report on SaaS, which I assume is Fusion Applications Cloud
    • To access this report from outside Fusion, in this case APEX, you need to call a web service which I assume Fusion exposes
    • I assume you are running APEX from an Oracle database on PaaS or IaaS on OCI (NOT apex.oracle.com and NOT Autonomous Database Cloud). Having that in mind, you should have access to the database as a DBA and to Linux/Windows a console as an oracle user (or some administrator of the database installation)
    • Based on your print screens, it looks like you you need to create an ACL entry to allow APEX/ORDS to call a web service from your parsing schema. In order to create an ACL entry with a PL/SQL statement, you need to connect with SQL*Plus or any other tool (SQL Developer for example) as SYS user and create that entry. SQL Workshop won't work since it executes as the parsing schema user.
    • Finally, you need to add the certificate which contains authentication and signing credentials, which will allow you to access the BI Publisher web service. This can be done at the command line (Linux or Windows) to launch the Oracle Wallet Manager.

    So if these steps are not your understanding, please clarify your expected process.

    Hope this helps!