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
  • January 2, 2017 | 17 views
  • Comments