aspose file tools*
The moose likes Web Services and the fly likes Adding complex type to a user defined type param, getting ORA-01427 error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Java » Web Services
Bookmark "Adding complex type to a user defined type param, getting ORA-01427 error " Watch "Adding complex type to a user defined type param, getting ORA-01427 error " New topic
Author

Adding complex type to a user defined type param, getting ORA-01427 error

Matt Kohanek
Village Idiot
Ranch Hand

Joined: Apr 04, 2009
Posts: 483

Hi

I have a PL SQL procedure with a param that is a user defined type. That type contains simple params (VARCHAR2, NUMBER). I have added a complex type to it. I am now trying to edit our BPEL process to reflect this. I have done something similar before without trouble, and it worked fine. This time however, when I invoke the BPEL, I get the following:

<bpelFault>
<faultType>0</faultType>
<bindingFault>
<part name="summary">
<summary>Exception occured when binding was invoked. Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'NIC_IMPORT' failed due to: Parse struct conversion error. An error occurred while parsing XML representing a Java struct. Unable to convert the XSD element WEB_TXN_REFERENCES whose user defined type is APPS.WEB_REFERENCES to a Java struct. Cause: java.sql.SQLException: ORA-01427: single-row subquery returns more than one row ". The invoked JCA adapter raised a resource exception. Please examine the above error message carefully to determine a resolution. </summary>
</part>
<part name="detail">
<detail>ORA-01427: single-row subquery returns more than one row </detail>
</part>
<part name="code">
<code>1427</code>
</part>
</bindingFault>
</bpelFault>

WEB_REFERENCES is the new complex type. It is just a type made up of a table of another user defined type:
(CREATE OR REPLACE TYPE APPS.WEB_REFERENCES IS TABLE OF WEB_REFERENCE; ).

That second type (WEB_REFERENCE) contains three params.

So basically my procedure has some simple parameters, and a complex parameter. I am trying to add the above complex type to the complex parameter.

This might help visualize it:

param1 VARCHAR2
param2 LINE_TYPE
-->sub_param1 VARCHAR2
-->sub_param2 WEB_REFERENCES
---->sub2_param WEB_REFERENCE
-------->param1 VARCHAR2
-------->param2 VARCHAR2
-------->param3 VARCHAR2

Can anyone give some guidance based on this error message?


True wisdom is in knowing you know nothing - Socrates
Matt Kohanek
Village Idiot
Ranch Hand

Joined: Apr 04, 2009
Posts: 483

I was using a db:type of Struct where I should have been using Array - hopefully this helps someone else if they come across this...
The annoying part is, I know that I thought of that earlier and tried the change, but something else must have been wrong at the time, because it didn't resolve things for me then.
To fix this, I just started the project over and generated the xsd from scratch rather than trying to go in and edit the files that already existed.
 
jQuery in Action, 2nd edition
 
subject: Adding complex type to a user defined type param, getting ORA-01427 error