When calling a Procedure with in a package in the DB Adapter and when tested the process from Enterprise Manager, I received the below error –
Error:The selected operation process could not be invoked. An exception occured while invoking the webservice operation. Please see logs for more details. oracle.sysman.emSDK.webservices.wsdlapi.SoapTestException: Exception occured when binding was invoked. Exception occured during invocation of JCA binding: “JCA Binding execute of Reference operation ‘CGPSSD_NGS_PAYROLL_GL_SUM_STG’ failed due to: Stored procedure invocation error. Error while trying to prepare and execute the APPS.CGPSSD_FMW_PS_TO_GL_INTERFACE.POPULATE_SUMMARY_TBL API. An error occurred while preparing and executing the APPS.CGPSSD_FMW_PS_TO_GL_INTERFACE.POPULATE_SUMMARY_TBL API. Cause: java.sql.SQLSyntaxErrorException: ORA-02089: COMMIT is not allowed in a subordinate session ORA-06512: at “APPS.CGPSSD_FMW_PS_TO_GL_INTERFACE”, line 249 ORA-06512: at line 1
Reason: The procedure has DML statements in it, so we have to have commit statement. Here the Datasource created in Weblogic server was XA Datasource. The XA Datasource always used for “global transaction” that may span multiple resources. But in our scenario, we are trying to commit the DML which comes under local transaction.
Remove commit statements from stored procedure or Use non-XA data source.
I tried removing the Commit statement from the procedure and it worked fine.
I tired the second option, non-XA data source as well and it worked.