developers

    Forum Stats

  • 3,873,656 Users
  • 2,266,622 Discussions
  • 7,911,597 Comments

Discussions

Mapping the output from Refcursor

ChandanaB
ChandanaB Member Posts: 12
edited Jun 6, 2012 2:46AM in XQuery
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/[email protected]</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/[email protected]</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>

Answers

  • 934699
    934699 Member Posts: 2
    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
  • 919712
    919712 Member Posts: 47
    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
This discussion has been closed.
developers