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>

By

Karkuvelraja T