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
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

I have a package and 2 functions inside that, I need to use those 2 functions inside my BI Publisher Data Template,
Package1
{
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 -->
<properties>
<property name="scalable_mode" value="off"/>
<property name="debug_mode" value="off"/>
</properties>
<!-- Parameters -->
<parameters>
<parameter name="Param1" dataType="varchar2"/>
<parameter name="Param2" dataType="varchar2"/>
</parameters>
<!-- Lexicals -->
<lexicals/>
<!-- SQL Queries -->
<dataQuery>
<sqlStatement name="Q_Container_Query">
<![CDATA[
Select Package1.Function1(:param1,:param2) from dual;
]]>
</sqlStatement>

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

</dataQuery>
<!-- Grouping -->
<dataStructure>
<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"/>

</group>

</group>
</dataStructure>
</dataTemplate>

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.


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


Current Time: Fri Mar 29 06:41:27 CDT 2024