We are developing an integration which sends data from one system to an Oracle E-Business Suite instance. In order to load the data into E-Business we need to call API’s which require the fnd_global.apps_initialize to be run.
We have a custom PL/SQL procedure which calls the API
SOA -> custom PL/SQL -> E-Biz API
The custom PL/SQL runs apps_initialize.
The problem is when we call this from SOA via a XA connection we get the following error:
<P_ERR_MESSAGE>Error - Failed to delete the interface records after reading result! Message: Unknown Error ORA-20001: Oracle error -20001: ORA-20001: Oracle error -2074: ORA-02074: cannot SET NLS in a distributed transaction
has been detected in fnd_global.set_nls.set_parameter('NLS_LANGUAGE','AMERICAN').
has been detected in fnd_global.set_nls. ORA-06512: at "APPS.APP_EXCEPTION", line 72
ORA-06512: at "APPS.FND_GLOBAL", line 245
ORA-06512: at "APPS.FND_GLOBAL", line 1426
ORA-06512: at "APPS.FND_GLOBAL", line 1684
ORA-06512: at "APPS.FND_GLOBAL", line 2309
ORA-06512: at "APPS.FND_GLOBAL", line 2447
ORA-06512: at "APPS.FND_GLOBAL", line 2385
ORA-06512: at "APPS.ICSOA_UTIL_PKG", line 42
ORA-06512: at "APPS.ICAR_INT_PKG", line 521
The main error here is cannot SET NLS in a distributed transaction.
This is caused by fnd_global.apps_initialize running SET NLS commands in order to do it’s thing.
We are looking to use the functionality of global transactions in order to achieve reliable messaging.
Does anyone have any experience with calling custom PL/SQL from SOA XA connections?
I have worked out that the Oracle Apps Adapter runs apps_initialize itself and we can control the username/responsibility by setting the jca.apps.username and jca.apps.responsibility properties.
I have the similar use case. I am using a seeded Oracle API to insert data into the tables. I want the user information to be populated in the WHO columns (CREATED_BY, LAST_UPDATED_BY). I have mentioned the jca.apps.username and jca.apps.responsibility in the Invoke activity.
But unfortunately, the columns are not populated with the data. I am using the Oracle Apps Adapter to call the API.
This issue can be resolved by using a non XA data source connection type instead of XA data source. I faced this issue and was able to resolve by creating a new connection with non XA type and use it in the adapter JNDI.