Skip to main content

Call Oracle package in Oracle BI Publisher 10g

Objective:

To call Oracle package in Oracle BI Publisher 10g.

Scenario:

Oracle BI Publisher is just a reporting tool. From which we can download excel, csv, xml, rtf, PDF reports. In which we can use only SQL statement not PL/SQL. But, we came across the scenario to call Oracle package in OBIP.  

Solution:

We can call the Oracle package in OBIP by Data template SQL.

Step 1: Crete OBIP report

Step 2: Create data template dataset using below SQL

<dataTemplate name="AIRReport" description="Industrial_MIS_Automation" 
                  dataSourceRef="BNP_RPTLIVDB_APEXRPS" Version="1.0" 
                          defaultPackage="pkg_sbfs_report_log">
                                                                <dataQuery>
                                                                                <sqlStatement name="STMT1">
                                                                                                <![CDATA[SELECT INDUS FROM DS_SBFS_MNTHLY_DATA_ANALYSIS]]>
                                                                                </sqlStatement>
                                                                </dataQuery>
        <dataTrigger name="beforeReportTrigger" 
                source="pkg_sbfs_report_log.Report_log('138','START')"/>
                                                                <dataStructure>
                                                                                <group dataType="varchar2" name="G_STMT_1" source="STMT1">
                                                                                                <element dataType="varchar2" name="INDUS" value="INDUS"/>
                                                                                </group>
                                                                </dataStructure>
        <dataTrigger name="afterReportTrigger" 
                source="pkg_sbfs_report_log.Report_log('138','END')"/>

                                                </dataTemplate>

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

Oracle Application Express Views (APEX)

Application Express Views Search SELECT * FROM apex_dictionary WHERE column_id = 0; View Comment Parent View APEX_APPLICATIONS Applications defined in the current workspace or database user. APEX_WORKSPACES APEX_APPLICATION_ALL_AUTH All authorization schemes for all components by Application APEX_APPLICATIONS ...
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