重庆思庄oracle技术分享-merge语句中存在两个长绑定变量导致ORA-03137

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

SYMPTOMS
Merge statement fails with:
ORA-03137: TTC protocol internal error : [12333] [254] [64] [123] [] [] [] []

The merge statement as such uses many bind variables as shown by the current sql in the trace file:
----- Current SQL Statement for this session (sql_id=…) -----
MERGE INTO ORDER_ USING (SELECT :1 SystemOrderId FROM DUAL) I ON (ORDER_.SystemOrderId=I.SystemOrderId)
WHEN MATCHED THEN UPDATE SET SourceSystem=:2 ,AssetType=:3 ,OrderType=:4 , …
WHEN NOT MATCHED THEN INSERT (SourceSystem, AssetType, OrderType, …) VALUES (:49 ,:50 ,:51 , …)

Note that the call stack of an ORA-3137 [12333] is rather generic and hardly useable to identify a specific issue.

CAUSE
Merge is using two LONG bind variables as shown in the trace file:
…
Bind#44
oacdty=01 mxl=4001(4528) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=873 siz=4000 off=0
kxsbbbfp=ffffffff7cd20d50 bln=4000 avl=4001 flg=25

Bind#92
oacdty=01 mxl=4001(4528) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=873 siz=4000 off=0
kxsbbbfp=ffffffff7cd3cde0 bln=4000 avl=4001 flg=25

Using LONG bind variables is not supported as clarified in
Bug 20021406 - ORA-3137: TTC PROTOCOL INTERNAL ERROR : [12333] ON MERGE STATEMENT
which was closed with status “Not a Bug”.

The bug report states: “Oracle server interface is not designed to take LONG bind as duplicate bind OR more than one LONG bind (bind length more than 4K). So this is not supported.”

From the trace file, we can confirm that two bind variables are used for respectively updating and inserting on the same LONG column.

SOLUTION
The merge statement failing is the expected behaviour as it is not supported to have two long bind variables on the same SQL statement.

To solve the issue, please change the MERGE statement and avoid to have two long bind variables on the same SQL statement.