1 2 Previous Next 25 Replies Latest reply on Apr 7, 2006 5:17 PM by 428263 Go to original post
      • 15. Re: New problem calling PL/SQL based WS from BPEL process
        ow008057
        Hi Andrea,

        i understand you have a problem with the database adapter and you want it resolved.

        The problem is i feel that my topic has been hijacked! Ans i also like my problem to be resolved.

        This thread was originally about calling a PL/SQL based web service from BPEL with an XML-string as parameter.

        Can you please create a new topic for your problem? This also simplifies queries on this forum. Thanks.
        • 16. Re: New problem calling PL/SQL based WS from BPEL process
          ow008057
          Bo,
          thanks for your suggestion to use the DB Adapter.

          Using objects could be a solution, but will require quite some rework on our current functionality that processes strings containing XML (or we have to create a additional function as an interface to the current functions). The XML can contain between 10 and 60 elements, several levels deep. So i try to stick to the present set up, if possible.

          Sandor,
          i have send you an e-mail but it bounced as zipped attachements are not accepted by Oracle. I will resend you the e-mail with a "zap" file as attachement. See if that works.
          • 17. Re: New problem calling PL/SQL based WS from BPEL process
            Sandor Nieuwenhuijs-Oracle
            Bart, indeed, please rename to e.g. .ZAP and resend.
            Sandor
            • 18. Re: New problem calling PL/SQL based WS from BPEL process
              428263
              The problem is with the XSD, specifically the element definition for CATLOG. An attribute is missing, db:index. The CATLOG element definition should read

              <element name="CATLOG" db:type="CAT" db:index="1" .. />

              If the XSD was generated by the XSD generator using the DB Adapter Wizard, this would have been present, along with minOccurs and maxOccurs.

              The attribute is used as an optimization during both design time when the XSD is generated and at runtime when the stored procedure is built and executed.

              This attribute indicates the numeric index (position) of the parameter represented by the XSD element in the signature of the stored procedure. Although usually obvious what these indices are, these attributes must be present for each such XSD element. They are not required for elements of an object definition so they should not appear in the definition of the CAT complexType.

              Generally, it is safest to use the XSD generator provided by the DB Adapter Wizard as opposed to writing an XSD by hand if that is the case here. Editing the generated XSDs is also not advised.
              • 19. Re: New problem calling PL/SQL based WS from BPEL process
                428263
                I should also point out some additional problems with the element definition for the parameter CATLOG. A correction is required as follows

                <element name="CATLOG" type="db:CAT" db:type="Struct" db:index="1" .. />

                The "Struct" indicates that the parameter is a user defined type (UDT). Then you would have a complexType definition for "CAT" as you have in the original XSD.
                • 20. Re: New problem calling PL/SQL based WS from BPEL process
                  428263
                  It is obvious that the XSD generator was not used to produce the original XSD and that this was likely written by hand. There is yet an additional problem with it as it stands currently. There should be a root element called "InputParameters" that is a complexType with one element in the sequence, the element definition for CATLOG. The complexType definition for CAT should be at the root level. So, another correction would be

                  <element name="InputParameters">
                  <complexType>
                  <sequence>
                  <element name="CATLOG" ../>
                  </sequence>
                  </complexType>
                  </element>
                  <complexType name="CAT">
                  ..
                  </complexType>

                  The XSD generator annotates the XSD with specific information that is expected and used by the runtime to build and execute the stored procedure. The structure of the XSD is relatively fixed for input and output parameters. As an additional note, if there were any IN/OUT or OUT parameters, then a similar root element would exist, called "OutputParameters".
                  • 21. Re: New problem calling PL/SQL based WS from BPEL process
                    428995
                    Thank you very much Michael now I have understood the structure of XSD for stored proc and now all runs!!!

                    Bart I'm sorry for the "intrusion", my problem is similar to yours and I solved it with DBAdapter, so DBAdapter could be also your solution...

                    Thank you guys!!!

                    Andrea
                    • 22. Re: New problem calling PL/SQL based WS from BPEL process
                      Sandor Nieuwenhuijs-Oracle
                      Bart's error is resolved, there was an error in the assign activity.
                      Sandor
                      • 23. Re: New problem calling PL/SQL based WS from BPEL process
                        4501
                        Hello,

                        I had the same type of errors with InterConnect when sending SOAP calls over an HTTP interface.
                        <bedrag xmlns="">27,95</bedrag>
                        The empty namespace is caused by an error in the xmlparserv2.jar. I obtained a testversion of xmlparserv2.jar from support where this error is removed.
                        So this unbugged xmlparserv2.jar should be added to the BPEL product.

                        Regards Leon Smiers
                        • 24. Re: New problem calling PL/SQL based WS from BPEL process
                          502399
                          Hi Sandor,

                          I am trying to call GET_ORDER oracle apps API, using BPEL PM-> Adapter Configuration wizard->for Oracle Apps.

                          I am passing valid values to the IN parameters P_API_VERSION_NUMBER as 1.0
                          and P_HEADER_ID as 94441. But when I run the process every time I am getting the below message. I get this error even though I send empty values. Can you please help me in resolving this. Shall I send you my project code base.

                          Error Message:

                          <summary>file:/D:/OraBPELPM_2/integration/orabpel/domains/default/tmp/.bpel_OAAPI_1.0.jar/BPEL.wsdl [ BPEL_ptt::BPEL(InputParameters,OutputParameters) ] - WSIF JCA Execute of operation 'BPEL' failed due to: Error while trying to prepare and execute an API.
                          An error occurred while preparing and executing the APPS.XX_BPEL_BPEL.OE_ORDER_PUB$GET_ORDER API. Cause: java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error
                          ORA-06512: at "APPS.XX_BPEL_BPEL", line 1
                          ORA-06512: at "APPS.XX_BPEL_BPEL", line 1
                          ORA-06512: at line 1

                          ; nested exception is:
                               ORABPEL-11811
                          Error while trying to prepare and execute an API.
                          An error occurred while preparing and executing the APPS.XX_BPEL_BPEL.OE_ORDER_PUB$GET_ORDER API. Cause: java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error
                          ORA-06512: at "APPS.XX_BPEL_BPEL", line 1
                          ORA-06512: at "APPS.XX_BPEL_BPEL", line 1
                          ORA-06512: at line 1

                          Check to ensure that the API is defined in the database and that the parameters match the signature of the API. Contact oracle support if error is not fixable.
                          </summary>

                          Thanks
                          Svan
                          • 25. Re: New problem calling PL/SQL based WS from BPEL process
                            428263
                            The "numeric or value error" you are seeing is a PL/SQL exception thrown from within the stored procedure you are executing. The adapter is simply propagating it up to the BPEL process. Make sure that you are passing correct values for all parameters required for the procedure to execute normally. A quick way to verify the minimal set of parameters and their values is to invoke the procedure using SQL*Plus. Once that works, you'll know what information needs to go into the instance XML.
                            1 2 Previous Next