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:
<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>
<detail>ORA-01427: single-row subquery returns more than one row </detail>
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.
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.
subject: Adding complex type to a user defined type param, getting ORA-01427 error