[URGENT HELP] ORA-02051 encountered for DBAdapter
573296May 25 2011 — edited May 26 2011Dear experts,
Platform : SOA Suite 11.1.1.3
We are using FileAdapter+Mediator+DbAdapter to insert/update records into OracleDB. And DBAdapter use 'Pure SQL' operation with Merge SQL statement.
If the records number < 30, then can be proceeded without problem. But if the records is huge, like > 500, then "ORA-02051: another session in same transaction failed" error throwed. We are using Multiple Datasource configed on weblogic.
I've tried to search from web and metalink, but seems few information about this error. So would you please give me help on it?
Thank you in advance.
------------------------------------------------------------------
The detailed exception as below.
[composite_instance_id: 427032] Post 2 mess failed[[
oracle.fabric.common.FabricInvocationException: BINDING.JCA-12563
Exception occured when binding was invoked.
Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'mergeMktgOfferSubPkgContnDB' failed due to: Pure SQL Exception.
Pure SQL Execute of MERGE INTO edrive.mktg_offer_sub_pkg_contnt b USING (SELECT modl_desgtr_cd FROM (SELECT modl_desgtr_cd FROM edrive.mktg_offer_sub_pkg_contnt WHERE originating_domain_cd = ? AND mktg_offer_id = ? AND sellng_src_cd = ? AND namplt_cd = ? AND modl_yr_nbr = ? AND modl_yr_sufx_cd = ? AND modl_desgtr_cd = ? AND pkg_optn_cd = ? AND sub_pkg_optn_cd = ? AND optn_cd = ? UNION ALL SELECT NULL modl_desgtr_cd FROM DUAL) WHERE ROWNUM = 1) a ON (a.modl_desgtr_cd = b.modl_desgtr_cd) WHEN MATCHED THEN UPDATE SET b.avbl_incld_flag = ?, b.actn_cd = 'U', b.updt_timstm = SYSDATE WHERE originating_domain_cd = ? AND mktg_offer_id = ? AND sellng_src_cd = ? AND namplt_cd = ? AND modl_yr_nbr = ? AND modl_yr_sufx_cd = ? AND modl_desgtr_cd = ? AND pkg_optn_cd = ? AND sub_pkg_optn_cd = ? AND optn_cd = ? WHEN NOT MATCHED THEN INSERT (originating_domain_cd, mktg_offer_id, sellng_src_cd, namplt_cd, modl_yr_nbr, modl_yr_sufx_cd, modl_desgtr_cd, pkg_optn_cd, sub_pkg_optn_cd, optn_cd, avbl_incld_flag, actn_cd, updt_timstm, insert_timstm) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'I', SYSDATE, SYSDATE) failed.
Caused by java.sql.SQLSyntaxErrorException: ORA-02051: another session in same transaction failed