Skip to main content

Integration of Oracle BI Publisher 10g with Oracle Apex 4.2

Objective:


To integrate Oracle BI Publisher with Oracle Apex.

 Step 1: Create a procedure using below given PLSQL coding, which integrate Oracle BI with Oracle Apex.

CREATE OR REPLACE PROCEDURE "WEB_SERVICES_CALL" (
   p_name1         IN  VARCHAR2 DEFAULT NULL,
   p_value1          IN   VARCHAR2 DEFAULT NULL,
   p_name2            IN  VARCHAR2 DEFAULT NULL,
   p_value2            IN   VARCHAR2 DEFAULT NULL,
   p_name3            IN  VARCHAR2 DEFAULT NULL,
   p_value3           IN  VARCHAR2 DEFAULT NULL,
   p_name4            IN   VARCHAR2 DEFAULT NULL,
   p_value4          IN   VARCHAR2 DEFAULT NULL,
   p_name5               IN   VARCHAR2 DEFAULT NULL,
   p_value5           IN   VARCHAR2 DEFAULT NULL,
   p_name6            IN  VARCHAR2 DEFAULT NULL,
   p_value6            IN  VARCHAR2 DEFAULT NULL,
   p_name7             IN  VARCHAR2 DEFAULT NULL,
   p_value7           IN  VARCHAR2 DEFAULT NULL,
   p_name8           IN  VARCHAR2 DEFAULT NULL,
   p_value8           IN  VARCHAR2 DEFAULT NULL,
   p_name9         IN  VARCHAR2 DEFAULT NULL,
   p_value9            IN  VARCHAR2 DEFAULT NULL,
   p_name10        IN  VARCHAR2 DEFAULT NULL,
   p_value10            IN  VARCHAR2 DEFAULT NULL,
   p_name11        IN  VARCHAR2 DEFAULT NULL,
   p_value11       IN  VARCHAR2 DEFAULT NULL,
   p_name12           IN   VARCHAR2 DEFAULT NULL,
   p_value12          IN  VARCHAR2 DEFAULT NULL,
   p_name13         IN  VARCHAR2 DEFAULT NULL,
   p_value13      IN  VARCHAR2 DEFAULT NULL,
   p_name14           IN  VARCHAR2 DEFAULT NULL,
   p_value14          IN  VARCHAR2 DEFAULT NULL,
   p_template_name    IN  VARCHAR2 DEFAULT NULL,
   p_bi_user_name     IN  VARCHAR2,
   p_bi_password      IN  VARCHAR2,
   p_template_path    IN  VARCHAR2,
   p_file_name        IN  VARCHAR2,
   p_format           IN  VARCHAR2
)
AS
   l_mime      VARCHAR2 (48);
   l_name      VARCHAR2 (4000);
   l_env       CLOB;
   l_base64    CLOB;
   l_blob      BLOB;
   l_size      NUMBER;
   l_xml       XMLTYPE;
   l_ns        VARCHAR2 (4000) DEFAULT NULL;
   l_path      VARCHAR (255)   DEFAULT '//multiRef';
   l_url       VARCHAR2 (4000);
BEGIN
   l_env :=
         '<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pub="http://xmlns.oracle.com/oxp/service/PublicReportService" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
<soapenv:Header/>
   <soapenv:Body>
      <pub:runReport soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
         <reportRequest xsi:type="pub:ReportRequest">
            <attributeFormat xsi:type="xsd:string">'
      || p_format
      || '</attributeFormat>
<parameterNameValues xsi:type="pub:ArrayOfParamNameValue" soapenc:arrayType="pub:ParamNameValue[]"/>
      <attributeTemplate>'
      || p_template_name
      || '</attributeTemplate>
<parameterNameValues>
<item><multiValuesAllowed>true</multiValuesAllowed><name>'
      || p_name1
      || '</name>
<values><item>'
      || p_value1
      || '</item></values></item>
<item><multiValuesAllowed>true</multiValuesAllowed><name>'
      || p_name2
      || '</name>
<values><item>'
      || p_value2
      || '</item></values></item>
<item><multiValuesAllowed>true</multiValuesAllowed><name>'
      || p_name3
      || '</name>
<values><item>'
      || p_value3
      || '</item></values></item>
<item><multiValuesAllowed>true</multiValuesAllowed><name>'
      || p_name4
      || '</name>
<values><item>'
      || p_value4
      || '</item></values></item>
<item><multiValuesAllowed>true</multiValuesAllowed><name>'
      || p_name5
      || '</name>
<values><item>'
      || p_value5
      || '</item></values></item>
<item><multiValuesAllowed>true</multiValuesAllowed><name>'
      || p_name6
      || '</name>
<values><item>'
      || p_value6
      || '</item></values></item>
<item><multiValuesAllowed>true</multiValuesAllowed><name>'
      || p_name7
      || '</name>
<values><item>'
      || p_value7
      || '</item></values></item>
<item><multiValuesAllowed>true</multiValuesAllowed><name>'
      || p_name8
      || '</name>
<values><item>'
      || p_value8
      || '</item></values></item>
<item><multiValuesAllowed>true</multiValuesAllowed><name>'
      || p_name9
      || '</name>
<values><item>'
      || p_value9
      || '</item></values></item>
<item><multiValuesAllowed>true</multiValuesAllowed><name>'
      || p_name10
      || '</name>
<values><item>'
      || p_value10
      || '</item></values></item>
<item><multiValuesAllowed>true</multiValuesAllowed><name>'
      || p_name11
      || '</name>
<values><item>'
      || p_value11
      || '</item></values></item>
<item><multiValuesAllowed>true</multiValuesAllowed><name>'
      || p_name12
      || '</name>
<values><item>'
      || p_value12
      || '</item></values></item>
<item><multiValuesAllowed>true</multiValuesAllowed><name>'
      || p_name13
      || '</name>
<values><item>'
      || p_value13
      || '</item></values></item>
<item><multiValuesAllowed>true</multiValuesAllowed><name>'
      || p_name14
      || '</name>
<values><item>'
      || p_value14
      || '</item></values></item>
</parameterNameValues>
<reportAbsolutePath xsi:type="xsd:string">'
      || p_template_path
      || '</reportAbsolutePath>';
  IF v ('BIPUB_VERSION') = '10.1.3.4.0'
   THEN
      l_env :=
             l_env || '<sizeOfDataChunkDownload>-1</sizeOfDataChunkDownload>';
      l_path := '//runReportReturn';
   END IF;
l_env :=
         l_env
      || '</reportRequest>
         <userID xsi:type="xsd:string">'
      || p_bi_user_name
      || '</userID>
         <password xsi:type="xsd:string">'
      || p_bi_password
      || '</password>
      </pub:runReport>
   </soapenv:Body>
</soapenv:Envelope>';
   l_url := 'http://' || v ('BIPUB_SERVER');
  IF v ('BIPUB_PORT') IS NOT NULL
   THEN
      l_url := l_url || ':' || v ('BIPUB_PORT');
   END IF;
 l_url := l_url || '/xmlpserver/services/PublicReportService';
   l_xml := flex_ws_api.make_request (p_url => l_url, p_envelope => l_env);
 IF l_xml.EXISTSNODE ('//faultstring') = 1
   THEN
raise_application_error
                       (-20001,
                        l_xml.EXTRACT ('//faultstring/text()').getstringval
                                                                           ()
                       );
   END IF;
  l_ns := v ('NS');
   l_mime :=
      flex_ws_api.parse_xml (l_xml,
                             l_path || '/reportContentType/text()',
                             l_ns
                            );
   l_name := p_file_name;                                            --||'.xls';
   l_base64 :=
      flex_ws_api.parse_xml_clob (l_xml, l_path || '/reportBytes/text()',
                                  l_ns);
   l_blob := flex_ws_api.clobbase642blob (l_base64);
   l_size := DBMS_LOB.getlength (l_blob);
   HTP.init;
   OWA_UTIL.mime_header (NVL (l_mime, 'application/octet'), FALSE);
   HTP.p ('Content-length: ' || l_size);
   HTP.p (   'Content-Disposition:  attachment; filename="'
          || REPLACE (REPLACE (l_name, CHR (10), NULL), CHR (13), NULL)
          || '"'
         );
   OWA_UTIL.http_header_close;
   WPG_DOCLOAD.download_file (l_blob);
   apex_application.g_unrecoverable_error := TRUE;
END;

Step 2: Execute the Procedure “web_service_call” in SQL Developer/Toad.

Step 3: Create Process in Oracle Apex on which call the procedure “web_service_call”

BEGIN
   web_services_call (  p_name1               => 'FDATE',
                       p_value1             => :p157_fromdate,
                       p_name2             => 'TDATE',
                       p_value2             => :p157_todate,
                       p_name3                => 'I_TYPE',
                       p_value3               => :p157_investor_type,
                       p_bi_user_name         => :p0_bi_user_name,
                       p_bi_password          => :p0_bi_password,
                       p_template_name       => 'Activity In Accounts Excel Template',
                       p_template_path        => ‘/BNP PARIBAS MUTUAL FUND - REPORTS/MIS_REPORTS_AUTOMATION/402 ACTIVITY IN ACCOUNTS/402 ACTIVITY IN ACCOUNTS.xdo’,
                       p_file_name           => 'Activity_In_Accounts.xls',
                      p_format              => 'excel'
                     );
END;
Note:    1.  FDATE   –  OBIP Item (FROM DATE) Name 
               2.  TDATE   –  OBIP Item (TO DATE) Name
               3.  I_TYPE   – OBIP Item (INVESTOR TYPE) Name
               4.  p_format  –  Format can be Excel,Csv,PDF…
               5.  :p157_fromdate   –  Get the value from Apex Item (Date Allotted From) 
               6.  :p157_todate  –  Get the value from Apex Item (Date Allotted To)
               7.  :p157_investor_type – Get the value from Apex Item (Investor Type) 
               8.  :p0_bi_user_name –  Get the value  from Apex Item (BI User Name)
               9.  :p0_bi_password –  Get the value  from Apex Item (BI Password)
            10.   p_template_path  - Template path has to take from particular
                                                          OBIP using Link to this report (Link) 

    Documents:

 Fig 1 : Apex Screen Design

     While Clicking Submit (Button) to call the below process in Apex,


Fig 2: Apex Process (Call the Procedure)


 Fig 3: OBIP Screen Design

Fig 4: Generate Link from OBIP


 Fig 5: Source of link (OBIP)

     Note: Can generate this report from Apex or OBIP

     Output 1: To generate report from OBIP using Apex



     Output 2: To generate report from OBIP

Comments

Post a Comment

Popular posts from this blog

Printing Page Numbers in RTF Template [Oracle BI Publisher]

Here I am giving an example to print the page numbers dynamically in the RTF (Rich Text Format) template. Step 1:  Go to page footer and copy and paste the below script. Page |  <?fo:page-number?>  of  <?fo:page-number-citation:xdofo:lastpage-joinseq?> <fo:page-number> :   This is the object, which is used to represent the current page-number. <?fo:page-number-citation:xdofo:lastpage-joinseq?> :  This is the syntax, which is used to represent the total number of pages. Step 2:  Load the XML and preview the result. Output: That's it. References: fo:page-number Printing Page Number Code in Oracle XMLP RTF Template
2 comments
Read more

Friendly URL: Redirect to Different Page after Login in Oracle APEX 20.1

Oracle has updated apex.oracle.com to APEX 20.1 which includes among other features the new Friendly URL option. Here i am giving an example to redirect to different page after login in Oracle APEX 20.1 [Friendly URL Enabled] Step 1: Define home page for each user in emp master table as below Step 2: To enable Friendly URL Syntax, follow below steps, 1) On the Workspace home page, click the App Builder icon. 2) Select an application (The Application home page appears). 3) From Application home page, you can access the Definition page in TWO ways: Click the Edit Application Properties button. From Shared Components:              1) Click Shared Components .              2) Under Application Logic, click Application Definition Attributes . The Definition page appears. 4) Under Properties, configure the Friendly URL s attribute: Click Apply Changes to save your ch...
Post a Comment
Read more

Save Selected Interactive Grid Records into a Collection - Oracle APEX

Here I am giving an example to save selected interactive grid records into a oracle apex collection. Step 1: Create a new blank page. Note: Mine was page 20. You will need to update reference to " P20 " with your page number if it's different. Step 2: Create a new interactive grid report region to the page using below query. Set Static Id "EmpDetails" to the region. SELECT  *     FROM   ( SELECT  emp . empno ,                emp . ename ,                emp . JOB ,                dept . dname department ,                dept . loc  LOCATION ,                mgr . ename  ...
9 comments
Read more