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.