Joining multiple queries in Data template XML Publisher

Joining multiple queries in XML Publisher can be done by 2 methods

1) Query Link for below dataQuery

 <link name="LINK1" parentQuery="Q1" parentColumn="VALUE1" childQuery="Q2" childColumn="COLUMN1"/>  
 <link name="LINK2" parentQuery="Q2" parentColumn="COLUMN1" childQuery="Q3" childColumn="COLUMN6"/>  

2) Column link between multiple queries
 Multiple queries can be linked in XML Data Template by passing the column from parent query as parameter to the child sqlStatement. This approach is recommended over using Query link and there is no limit for number of queries to be linked.

 <dataQuery>  
   <sqlStatement name="Q1">  
   <![CDATA[  
           SELECT XT1.VALUE1 from   
                XX_TABLE1 XT1  
                WHERE 1=1  
                     AND XT1.COLUMN1 = :PARAMETER1 <your parameter is optionalter>  
   ]]>  
   </sqlStatement>  
   <sqlStatement name="Q2">  
   <![CDATA[  
           SELECT XT2.COLUMN1, XT2.COLUMN2 from   
                XX_TABLE2 XT2  
                WHERE 1=1  
                     AND XT2.COLUMN1 = :VALUE1  
   ]]>  
   </sqlStatement>       
   <sqlStatement name="Q3">  
   <![CDATA[  
           SELECT XT3.COLUMN5, XT3.COLUMN6 from   
                XX_TABLE3 XT3  
                WHERE 1=1  
                     AND XT3.COLUMN6 = :COLUMN1  
   ]]>  
   </sqlStatement>  
 </dataQuery>       

The same can be represented in datastructure of XML Data template.

 <dataStructure>  
   <group name="G_PARENT"  source="Q1">  
           <element name="VALUE1"                value="VALUE1"/>       
           <group name="G_HDR_DETAILS"  source="Q2">  
                <element name="COLUMN1"                value="COLUMN1"/>  
                <element name="COLUMN2"                value="COLUMN2"/>  
                          <group name="G_LINE_DETAILS"  source="Q3">  
                               <element name="COLUMN5"        value="COLUMN5"/>  
                               <element name="COLUMN6"        value="COLUMN6"/>       
                          </group>                 
           </group>  
      </group>  
 </dataStructure>  

Oracle Procedure returns table Pl-Sql

Create a table type

CREATE OR REPLACE TYPE XX_REC_TYPE AS     OBJECT
    (
        a1 VARCHAR2(50) ,
        a2      VARCHAR2 (50)
        );


CREATE OR REPLACE TYPE XX_TBL_TYPE
IS
    TABLE OF XX_REC_TYPE;

Create a procedure with out arguments of table type


CREATE OR REPLACE PROCEDURE  XX_SVC(
        p_input in VARCHAR2,
        p_output1 out varchar2,
        p_XX_TBL_TYPE out XX_TBL_TYPE        )
AS
    ord_tbl XX_TBL_TYPE:=XX_TBL_TYPE ();
BEGIN

   select 'xxx' into p_output1 from dual;

    FOR r1 IN
    (
        SELECT  value1,value2 from xx_tbl
                AND value1 = p_input
    )
    LOOP
        ord_tbl.Extend;
        ord_tbl(ord_tbl.last) := XX_REC_TYPE (r1.value1,r1.value2);
    END LOOP;
   
    p_XX_TBL_TYPE := ord_tbl;

END;    


Call the procedure to return the values 

set serveroutput on
clear screen
declare
  lc_output varchar2(240);
  ord_tbl XX_TBL_TYPE;
begin
  XX_SVC(
        p_input =>'12345',
        p_output1 => lc_output,
        p_XX_TBL_TYPE => ord_tbl);
      
        dbms_output.put_line('p_output1-->'||lc_output);
      
        for i in ord_tbl.first .. ord_tbl.last loop
      
        dbms_output.put_line(ord_tbl(i).value1 || ' ' ||
                                ord_tbl(i).value2);
      
        end loop;
      
end;    

 

Oracle Function Returns Table Pl-Sql

Create a table type

CREATE OR REPLACE TYPE XX_REC_TYPE AS     OBJECT
    (
        a1 VARCHAR2(50) ,
        a2      VARCHAR2 (50)
        );


CREATE OR REPLACE TYPE XX_TBL_TYPE
IS
    TABLE OF XX_REC_TYPE;
    

create a function with return table type

 CREATE OR REPLACE FUNCTION XX_SVC(
        p_input VARCHAR2 )
    RETURN XX_TBL_TYPE
AS
    ord_tbl XX_TBL_TYPE:=XX_TBL_TYPE ();
BEGIN

    FOR r1 IN
    (
        SELECT  value1,value2 from xx_tbl
                AND value1 = p_input
    )
    LOOP
        ord_tbl.Extend;
        ord_tbl(ord_tbl.last) := XX_REC_TYPE (r1.value1,r1.value2);
    END LOOP;

RETURN( ord_tbl );

END;   

To call the function use the statement below

select *  from TABLE( XX_SVC('your_input') );