Sunday 1 January 2017

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

No comments:

Post a Comment