Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Mapping the output from Refcursor

ChandanaBJan 19 2011 — edited Jun 6 2012
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>

Comments

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
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
1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 4 2012
Added on Jan 19 2011
#xml, #xquery
2 comments
1,961 views