Home » Fusion Middleware & Colab Suite » Business Intelligence » Calling Oracle Functions in Data Template
Calling Oracle Functions in Data Template [message #449009] Fri, 26 March 2010 04:33
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member


I have a package and 2 functions inside that, I need to use those 2 functions inside my BI Publisher Data Template,
Function1(Param1 varchar2,param2 varchar2) return Refcursor
Open Refcursor for 
Select Fun1Col1,Fun1Col2,Fun1Col3 from ABCD;

Function2(Param1 varchar2 , Fun1Col1 varchar2,Fun1Col2 varchar2 ) return Refcursor
Open Refcursor for 
Select Fun2Col1,Fun2Col2,Fun2Col3 from XYZ where XXX=Fun1Col1 and YYY=Fun1Col2 and ZZZ=Param1;

Here the Second Function is using the Output Columns of First Function as Input Parameters.

The above functions are created for Oracle Reports which is working fine, Now I am converting them from Oracle Reports to BI Publisher and I am not able to proceed.

I have designed my Data Template as follows.

<dataTemplate name="XYZ " description="XYZ" defaultPackage="">
<!-- Property to make XML Output tags UPPERCASE -->
<property name="scalable_mode" value="off"/>
<property name="debug_mode" value="off"/>
<!-- Parameters -->
<parameter name="Param1" dataType="varchar2"/>
<parameter name="Param2" dataType="varchar2"/>
<!-- Lexicals -->
<!-- SQL Queries -->
<sqlStatement name="Q_Container_Query">
Select Package1.Function1(:param1,:param2) from dual;

<sqlStatement name="Q_Container_Item_Query">
Select Package1.Function2(:param1,:Fun1Col1,:FunCol2) from dual

<!-- Grouping -->
<group name="G_Container_Query" source="Q_Container_Query">
<element name="Fun1Col1" value="Fun1Col1"/>
<element name="Fun1Col2" value="Fun1Col2"/>
<element name="Fun1Col3" value="Fun1Col3"/>

<group name="G_Container_Item_Query" source="Q_Container_Item_Query">
<element name="Fun2Col1" value="Fun2Col1"/>
<element name="Fun2Col2" value="Fun2Col2"/>
<element name="Fun2Col3" value="Fun2Col3"/>



The point of concern is whether
Select Package1.Function2(:param1,:Fun1Col1,:FunCol2) from dual
is valid or not, because its accepting Function1's output values as Input Parameters.

Ashoka BL
Previous Topic: Oracle 1og reports to BI Publisher
Next Topic: OBI Publisher JDBC Connection Issue
Goto Forum:

Current Time: Fri Dec 04 21:02:21 CST 2020