Stack Exchange
How to write PL to return multiple rows which auto generates strong typed XSD via DB Adapter in Service Bus? (Oracle PL/SQL)
I am calling a procedure via DB adapter in Oracle Service Bus. I am able to select multiple rows from PL using SYS_REFCURSOR as the OUT parameter.
/*Package Specification*/

create or replace PACKAGE ACC_HIER AS 

  PROCEDURE f_Hier(
      IN_T_CODE IN HIER.T_CODE%TYPE,
      H_DETAILS OUT SYS_REFCURSOR);

END ACC_HIER;


/*Package Body*/

create or replace PACKAGE BODY ACC_HIER AS


  PROCEDURE f_Hier(
      IN_T_CODE IN HIER.T_CODE%TYPE,
      H_DETAILS OUT SYS_REFCURSOR) AS

  BEGIN

    OPEN H_DETAILS FOR
         SELECT T_LEVEL,T_CODE,P_ID,P_NAME,LAST_UPDATE_DATE
         FROM HIER
         WHERE T_CODE = IN_T_CODE
         ORDER BY T_LEVEL;
    EXCEPTION 
            WHEN OTHERS 
            THEN dbms_output.put_line('No Rows Found');
  END f_Hier;

END ACC_HIER;

When I import the package via DB adapter, it auto generates weak xsd:
<schema targetNamespace="http://xmlns.test.com/pcbpel/adapter/db/sp/DB_AccHierarchy_Pkg" xmlns="http://www.w3.org/2001/XMLSchema" xmlns:db="http://xmlns.test.com/pcbpel/adapter/db/sp/DB_AccHierarchy_Pkg" elementFormDefault="qualified">
   <element name="InputParameters">
      <complexType>
         <sequence>
            <element name="IN_T_CODE" type="string" db:index="1" db:type="VARCHAR2" minOccurs="0" nillable="true"/>
         </sequence>
      </complexType>
   </element>
   <element name="OutputParameters">
      <complexType>
         <sequence>
            <element name="H_DETAILS" type="db:RowSet" db:index="2" db:type="RowSet" minOccurs="0" nillable="true"/>
         </sequence>
      </complexType>
   </element>
   <complexType name="RowSet">
      <sequence>
         <element name="Row" minOccurs="0" maxOccurs="unbounded">
            <complexType>
               <sequence>
                  <element name="Column" maxOccurs="unbounded" nillable="true">
                     <complexType>
                        <simpleContent>
                           <extension base="string">
                              <attribute name="name" type="string" use="required"/>
                              <attribute name="sqltype" type="string" use="required"/>
                           </extension>
                        </simpleContent>
                     </complexType>
                  </element>
               </sequence>
            </complexType>
         </element>
      </sequence>
   </complexType>
</schema>

The attributes are generic and when we execute the DB Adapter the output is as:
<Row>
<Column name="T_LEVEL" sqltype="NUMBER">1</Column>
<Column name="T_CODE" sqltype="VARCHAR2">XYZ</Column>
<Column name="P_ID" sqltype="NUMBER">13214</Column>
<Column name="P_NAME" sqltype="VARCHAR2">XYZ_1 Limited</Column>
<Column name="LAST_UPDATE_DATE" sqltype="TIMESTAMP">2015-07-01T09:21:27.901107+05:30</Column>
</Row>
<Row>
<Column name="T_LEVEL" sqltype="NUMBER">2</Column>
<Column name="T_CODE" sqltype="VARCHAR2">XYZ</Column>
<Column name="P_ID" sqltype="NUMBER">156219</Column>
<Column name="P_NAME" sqltype="VARCHAR2">XYZ_2 Limited</Column>
<Column name="LAST_UPDATE_DATE" sqltype="TIMESTAMP">2015-07-02T09:21:27.901107+05:30</Column>
</Row>

Requirement: I want the XSD generated to be similar to following snippet:
  <complexType>
     <element name="T_LEVEL" type="decimal" nillable="true"/>
     <element name="T_CODE" type="string" nillable="true"/>
     <element name="P_ID" type="decimal" nillable="true"/>
     <element name="P_NAME" type="string" nillable="true"/>
     <element name="LAST_UPDATE_DATE" type="dateTime" nillable="true"/>
  </complexType>

and Output Similar to:
<Row>
<T_LEVEL>1</T_LEVEL>
<T_CODE>XYZ</T_CODE>
<P_ID>13214</P_ID>
<P_NAME>XYZ_1 Limited</P_NAME>
<LAST_UPDATE_DATE>2015-07-01T09:21:27.901107+05:30</LAST_UPDATE_DATE>
</Row>
<Row>
<T_LEVEL>2</T_LEVEL>
<T_CODE>XYZ</T_CODE>
<P_ID>156219</P_ID>
<P_NAME>XYZ_2 Limited</P_NAME>
<LAST_UPDATE_DATE>2015-07-02T09:21:27.901107+05:30</LAST_UPDATE_DATE>
</Row>

Is there any way that DB Adapter auto generates the XSD in required format? 
What changes in procedure can get me the required results? Any alternate solution?

Nameet, I believe expecting the DB Adapter to generate the XSD in the required format might not be the right approach.
I don't know if you can transform the result to the required format, but if so I think this will help:
FinalSchema.xsd
<?xml version="1.0" encoding="UTF-8" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.example.org"
            targetNamespace="http://www.example.org" elementFormDefault="qualified">
  <xsd:element name="DBRowList" type="DBRowListType"/>

  <xsd:complexType name="DBRowListType">
    <xsd:sequence>
      <xsd:element name="dbRow" type="DBRowType" nillable="true" maxOccurs="unbounded"/>
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="DBRowType">
    <xsd:sequence>
      <xsd:element name="T_LEVEL" type="xsd:decimal" nillable="true"/>
      <xsd:element name="T_CODE" type="xsd:string" nillable="true"/>
      <xsd:element name="P_ID" type="xsd:decimal" nillable="true"/>
      <xsd:element name="P_NAME" type="xsd:string" nillable="true"/>
      <xsd:element name="LAST_UPDATE_DATE" type="xsd:dateTime" nillable="true"/>
    </xsd:sequence>
  </xsd:complexType>
</xsd:schema>

Transform.xsl
<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:oraxsl="http://www.oracle.com/XSL/Transform/java"
                xmlns:DVMFunctions="http://www.oracle.com/XSL/Transform/java/com.bea.wli.sb.functions.dvm.DVMFunctions"
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xmlns:BasicCredentialsUserFunction="http://www.oracle.com/XSL/Transform/java/com.bea.wli.sb.stages.functions.BasicCredentialsUserFunction"
                xmlns:tns="http://www.example.org" xmlns:oracle-xsl-mapper="http://www.oracle.com/xsl/mapper/schemas"
                xmlns:UUIDUserFunction="http://www.oracle.com/XSL/Transform/java/com.bea.wli.sb.stages.functions.UUIDUserFunction"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:ns0="http://xmlns.test.com/pcbpel/adapter/db/sp/DB_AccHierarchy_Pkg"
                xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                xmlns:IsUserInRoleFunction="http://www.oracle.com/XSL/Transform/java/com.bea.wli.sb.stages.functions.IsUserInRoleFunction"
                xmlns:IsUserInGroupFunction="http://www.oracle.com/XSL/Transform/java/com.bea.wli.sb.stages.functions.IsUserInGroupFunction"
                xmlns:XrefFunctions="http://www.oracle.com/XSL/Transform/java/com.bea.wli.sb.functions.xref.XrefFunctions"
                exclude-result-prefixes="xsi oracle-xsl-mapper xsl xsd ns0 tns oraxsl DVMFunctions BasicCredentialsUserFunction UUIDUserFunction IsUserInRoleFunction IsUserInGroupFunction XrefFunctions">
  <oracle-xsl-mapper:schema>
    <!--SPECIFICATION OF MAP SOURCES AND TARGETS, DO NOT MODIFY.-->
    <oracle-xsl-mapper:mapSources>
      <oracle-xsl-mapper:source type="XSD">
        <oracle-xsl-mapper:schema location="DBSchema.xsd"/>
        <oracle-xsl-mapper:rootElement name="OutputParameters"
                                       namespace="http://xmlns.test.com/pcbpel/adapter/db/sp/DB_AccHierarchy_Pkg"/>
      </oracle-xsl-mapper:source>
    </oracle-xsl-mapper:mapSources>
    <oracle-xsl-mapper:mapTargets>
      <oracle-xsl-mapper:target type="XSD">
        <oracle-xsl-mapper:schema location="FinalSchema.xsd"/>
        <oracle-xsl-mapper:rootElement name="DBRowList" namespace="http://www.example.org"/>
      </oracle-xsl-mapper:target>
    </oracle-xsl-mapper:mapTargets>
    <!--GENERATED BY ORACLE XSL MAPPER 12.1.3.0.0(XSLT Build 140529.0700.0211) AT [FRI OCT 02 18:55:31 BRT 2015].-->
  </oracle-xsl-mapper:schema>
  <!--User Editing allowed BELOW this line - DO NOT DELETE THIS LINE-->
  <xsl:template match="/">
    <tns:DBRowList>
      <xsl:for-each select="/ns0:OutputParameters/ns0:H_DETAILS/ns0:Row">
        <tns:dbRow>
          <xsl:attribute name="nil" namespace="http://www.w3.org/2001/XMLSchema-instance"/>
          <xsl:if test="ns0:Column[@name='T_LEVEL']">
            <tns:T_LEVEL>
              <xsl:value-of select="ns0:Column[@name='T_LEVEL']"/>
            </tns:T_LEVEL>
          </xsl:if>
          <xsl:if test="ns0:Column[@name='T_CODE']">
            <tns:T_CODE>
              <xsl:value-of select="ns0:Column[@name='T_CODE']"/>
            </tns:T_CODE>
          </xsl:if>
          <xsl:if test="ns0:Column[@name='P_ID']">
            <tns:P_ID>
              <xsl:value-of select="ns0:Column[@name='P_ID']"/>
            </tns:P_ID>
          </xsl:if>
          <xsl:if test="ns0:Column[@name='P_NAME']">
            <tns:P_NAME>
              <xsl:value-of select="ns0:Column[@name='P_NAME']"/>
            </tns:P_NAME>
          </xsl:if>
          <xsl:if test="ns0:Column[@name='LAST_UPDATE_DATE']">
            <tns:LAST_UPDATE_DATE>
              <xsl:value-of select="ns0:Column[@name='LAST_UPDATE_DATE']"/>
            </tns:LAST_UPDATE_DATE>
          </xsl:if>
        </tns:dbRow>
      </xsl:for-each>
    </tns:DBRowList>
  </xsl:template>
</xsl:stylesheet>

Example Input (generated from DB Adapter):
<?xml version="1.0" encoding="UTF-8" ?>
<OutputParameters xmlns="http://xmlns.test.com/pcbpel/adapter/db/sp/DB_AccHierarchy_Pkg">
  <H_DETAILS>
    <Row>
      <Column name="T_LEVEL" sqltype="NUMBER">1</Column>
      <Column name="T_CODE" sqltype="VARCHAR2">XYZ</Column>
      <Column name="P_ID" sqltype="NUMBER">13214</Column>
      <Column name="P_NAME" sqltype="VARCHAR2">XYZ_1 Limited</Column>
      <Column name="LAST_UPDATE_DATE" sqltype="TIMESTAMP">2015-07-01T09:21:27.901107+05:30</Column>
    </Row>
    <Row>
      <Column name="T_LEVEL" sqltype="NUMBER">2</Column>
      <Column name="T_CODE" sqltype="VARCHAR2">XYZ</Column>
      <Column name="P_ID" sqltype="NUMBER">156219</Column>
      <Column name="P_NAME" sqltype="VARCHAR2">XYZ_2 Limited</Column>
      <Column name="LAST_UPDATE_DATE" sqltype="TIMESTAMP">2015-07-02T09:21:27.901107+05:30</Column>
    </Row>
  </H_DETAILS>
</OutputParameters>

Example Output:
<?xml version = '1.0' encoding = 'UTF-8'?>
<tns:DBRowList xmlns:tns="http://www.example.org" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.example.org file:/home/mfelipe/jdeveloper/mywork/POC/SBProject/FinalSchema.xsd">
   <tns:dbRow xmlns:ns0="http://www.w3.org/2001/XMLSchema-instance" ns0:nil="">
      <tns:T_LEVEL>1</tns:T_LEVEL>
      <tns:T_CODE>XYZ</tns:T_CODE>
      <tns:P_ID>13214</tns:P_ID>
      <tns:P_NAME>XYZ_1 Limited</tns:P_NAME>
      <tns:LAST_UPDATE_DATE>2015-07-01T09:21:27.901107+05:30</tns:LAST_UPDATE_DATE>
   </tns:dbRow>
   <tns:dbRow xmlns:ns1="http://www.w3.org/2001/XMLSchema-instance" ns1:nil="">
      <tns:T_LEVEL>2</tns:T_LEVEL>
      <tns:T_CODE>XYZ</tns:T_CODE>
      <tns:P_ID>156219</tns:P_ID>
      <tns:P_NAME>XYZ_2 Limited</tns:P_NAME>
      <tns:LAST_UPDATE_DATE>2015-07-02T09:21:27.901107+05:30</tns:LAST_UPDATE_DATE>
   </tns:dbRow>
</tns:DBRowList>

