File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Matt Kohanek
Village Idiot
Ranch Hand
Posts: 484
Java jQuery Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Matt Kohanek
Village Idiot
Ranch Hand
Posts: 484
Java jQuery Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic