2 Replies Latest reply: Jun 6, 2012 1:46 AM by 919712 RSS

    Mapping the output from Refcursor

    ChandanaB
      Hi,

      We are using a package where the output is a refcursor. I am calling this packaged procedure using a DB adapter in SOA. Now i want to map the values of a refcursor XSD to other XSD.

      If anyone has inputs on this, kindly share the same.

      RefCursor XSD
      --------------------
      <schema targetNamespace="http://xmlns.oracle.com/pcbpel/adapter/db/ICVM/GADGET_VERSION_CHECK_PKG/SP_VIEW_DETAILS/" xmlns="http://www.w3.org/2001/XMLSchema" xmlns:db="http://xmlns.oracle.com/pcbpel/adapter/db/ICVM/GADGET_VERSION_CHECK_PKG/SP_VIEW_DETAILS/" elementFormDefault="qualified">
      <element name="InputParameters">
      <complexType>
      <sequence>
      <element name="IN_GADGET_NAME" type="string" db:index="1" db:type="VARCHAR2" minOccurs="0" nillable="true"/>
      <element name="IN_GADGET_VERSION" type="string" db:index="2" db:type="VARCHAR2" minOccurs="0" nillable="true"/>
      </sequence>
      </complexType>
      </element>
      <element name="OutputParameters">
      <complexType>
      <sequence>
      <element name="IN_GADGET_NAME" type="string" db:index="1" db:type="VARCHAR2" minOccurs="0" nillable="true"/>
      <element name="IN_GADGET_VERSION" type="string" db:index="2" db:type="VARCHAR2" minOccurs="0" nillable="true"/>
      <element name="P_CURSOR" type="db:RowSet" db:index="3" db:type="RowSet" minOccurs="0" nillable="true"/>
      <element name="OUT_SAVE_STATUS" type="string" db:index="4" db:type="VARCHAR2" minOccurs="0" nillable="true"/>
      <element name="OUT_ERRORCODE" type="decimal" db:index="5" db:type="NUMBER" minOccurs="0" nillable="true"/>
      <element name="OUT_ERRORDESC" type="string" db:index="6" db:type="VARCHAR2" 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>

      Refcursor Output:
      ------------------------
      <soap-env:Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/" xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/">
      <soap-env:Body>
      <OutputParameters xmlns="http://xmlns.oracle.com/pcbpel/adapter/db/ICVM/GADGET_VERSION_CHECK_PKG/SP_VIEW_DETAILS/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <IN_GADGET_NAME>part</IN_GADGET_NAME>
      <IN_GADGET_VERSION xsi:nil="true"/>
      <P_CURSOR>
      <Row>
      <Column name="GADGET_NAME" sqltype="VARCHAR2">part</Column>
      <Column name="GADGET_DESCRIPTION" sqltype="VARCHAR2">test</Column>
      <Column name="GADGET_VERSION" sqltype="VARCHAR2">1.0</Column>
      <Column name="VERSION_DESCRIPTION" sqltype="VARCHAR2">test</Column>
      <Column name="EFFECTIVE_DATE" sqltype="DATE"/>
      <Column name="EXPIRY_DATE" sqltype="DATE"/>
      <Column name="VERSION_STATUS" sqltype="VARCHAR2">Curremt</Column>
      <Column name="DOWNLOAD_URL" sqltype="VARCHAR2">test</Column>
      </Row>
      <Row>
      <Column name="GADGET_NAME" sqltype="VARCHAR2">part</Column>
      <Column name="GADGET_DESCRIPTION" sqltype="VARCHAR2"/>
      <Column name="GADGET_VERSION" sqltype="VARCHAR2">2.0</Column>
      <Column name="VERSION_DESCRIPTION" sqltype="VARCHAR2"/>
      <Column name="EFFECTIVE_DATE" sqltype="DATE"/>
      <Column name="EXPIRY_DATE" sqltype="DATE">2010-12-15T00:00:00.000+05:30</Column>
      <Column name="VERSION_STATUS" sqltype="VARCHAR2">Current</Column>
      <Column name="DOWNLOAD_URL" sqltype="VARCHAR2">data1/gif/apps@wipro.com</Column>
      </Row>
      <Row>
      <Column name="GADGET_NAME" sqltype="VARCHAR2">part</Column>
      <Column name="GADGET_DESCRIPTION" sqltype="VARCHAR2"/>
      <Column name="GADGET_VERSION" sqltype="VARCHAR2">3.0</Column>
      <Column name="VERSION_DESCRIPTION" sqltype="VARCHAR2"/>
      <Column name="EFFECTIVE_DATE" sqltype="DATE"/>
      <Column name="EXPIRY_DATE" sqltype="DATE"/>
      <Column name="VERSION_STATUS" sqltype="VARCHAR2">CURRENT</Column>
      <Column name="DOWNLOAD_URL" sqltype="VARCHAR2">data1/gif/apps@wipro.com</Column>
      </Row>
      </P_CURSOR>
      <OUT_SAVE_STATUS xsi:nil="true"/>
      <OUT_ERRORCODE xsi:nil="true"/>
      <OUT_ERRORDESC xsi:nil="true"/>
      </OutputParameters>
      </soap-env:Body>
      </soap-env:Envelope>
        • 1. Re: Mapping the output from Refcursor
          934699
          Hi Chandana,

          You can use XSLT Transform to perform this task. I would suggest to use Varray as input. If you still need to use Ref Cursor then you have to manually map these elements & attributes and you wont be able to map using Jdeveloper's design editor.

          Thanks,
          Maulik
          • 2. Re: Mapping the output from Refcursor
            919712
            Hi Chandana,

            I am using the ref cursor as my out parameter. When i am testing this composite, i am getting the below error.

            Cause: java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'XXIER_MAIN_PROC' ORA-06550: line 1, column 7: PL/SQL: Statement ignored Check to ensure that the API is defined in the database and that the parameters match the signature of the API.

            Any Idea on this...

            Regards,
            Sudheer