My Oracle Support Banner

ORA-3137 [12333] On MERGE Statement Using A Bind Variable Larger Than 1000 bytes (Doc ID 2307683.1)

Last updated on AUGUST 04, 2018

Applies to:

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

Symptoms

If the merge statement have two long bind variables, then failing is the expected behavior. For details refer to Document : Merge is Failing with ORA-3137 [12333] (Doc ID 2039740.1)

But ORA-3137 [12333] error occurs although the merge statement have only a long bind variable.

There are two test cases for confirming this issue.

< TESTCASE 1 - using sqlplus on 12c >

drop user us_jdbc cascade;
create user us_jdbc identified by us_jdbc;
grant dba to us_jdbc;
connect us_jdbc/us_jdbc
DROP TABLE TEST;
CREATE TABLE TEST (A NUMBER(10), B VARCHAR2(10), C CLOB);

VARIABLE b1 NUMBER;
VARIABLE b2 VARCHAR2(2);
VARIABLE b3 VARCHAR2(5000);
EXEC :b1 := 1;
EXEC :b2 := 'VWXYZ';
EXEC :b3 := DBMS_RANDOM.STRING('L', 4000) || 'abc';

/*FIRST EXECUTION*/
MERGE INTO TEST
USING ( SELECT :b1 AS A, :b2 AS B FROM DUAL ) TEMP
ON ( TEST.A = TEMP.A AND TEST.B = TEMP.B )
WHEN NOT MATCHED THEN
INSERT (A, B, C) VALUES (:b1, :b2, :b3);

commit;

/*SECOND EXECUTION*/
MERGE INTO TEST
USING ( SELECT :b1 AS A, :b2 AS B FROM DUAL ) TEMP
ON ( TEST.A = TEMP.A AND TEST.B = TEMP.B )
WHEN NOT MATCHED THEN
INSERT (A, B, C) VALUES (:b1, :b2, :b3);

< Result >
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 31160
Session ID: 237 Serial number: 35438

< alert log >
ORA-03137: TTC protocol internal error : [12333] [254] [163] [15] [] [] [] []

 

< TESTCASE 2 - using JDBC on 12c or 11g >

- scource

$ cat JdbcTest.java
import java.sql.*;
import java.io.*;
import java.util.*;
import java.text.SimpleDateFormat;

public class JdbcTest {
static final String url = "jdbc:oracle:thin:@celcaix4.us.oracle.com:15678:sPc12Sa";

static public void main(String args[]) throws Exception {

DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Connection conn = DriverManager.getConnection (url, "us_jdbc", "us_jdbc");

System.out.println("before select");
simpleSelect(conn);
conn.close();
System.out.println("done");
}

static void simpleSelect(Connection conn) throws Exception {
String sql = "MERGE INTO TEST D USING (SELECT ? A FROM DUAL) S ON (D.A = S.A) WHEN MATCHED THEN UPDATE SET B=? WHEN NOT MATCHED THEN INSERT (A,B,C) VALUES (?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);

StringBuffer sb = new StringBuffer();
String ClobTest = "A";
for(int i = 0 ; i < 4001 ; i++) {
sb.append(ClobTest);
}

pstmt.setInt(1, 3);
pstmt.setString(2, "VWXYZ");
pstmt.setInt(3, 3);
pstmt.setString(4, "VWXYZ");
pstmt.setString(5, sb.toString());
pstmt.execute ();
conn.commit();
pstmt.close();
}
}

 

- compiling
export CLASSPATH=.:$ORACLE_HOME/jdbc/lib/ojdbc7.jar
/usr/java71_64/bin/javac JdbcTest.java

 

- executing
/usr/java71_64/bin/java -Doracle.jdbc.autoCommitSpecCompliant=false JdbcTest

 

< Result >
$ /usr/java71_64/bin/java -Doracle.jdbc.autoCommitSpecCompliant=false JdbcTest
before select
Exception in thread "main" java.sql.SQLRecoverableException: No more data to read from socket
at oracle.jdbc.driver.T4CMAREngineStream.unmarshalUB1(T4CMAREngineStream.java:456)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:397)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:943)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4901)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1385)
at JdbcTest.simpleSelect(JdbcTest.java:36)
at JdbcTest.main(JdbcTest.java:16)

< alert log >
Thu Sep 14 23:07:05 2017
ORA-03137: TTC protocol internal error : [12333] [254] [2] [15] [] [] [] []

 

We can find the bind information in incident trace file as:

----- Bind Info (kkscoacd) -----
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=01 siz=112 off=0
No bind buffers allocated
Bind#1
oacdty=01 mxl=32(05) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=01 siz=0 off=24
No bind buffers allocated
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=01 siz=0 off=56
No bind buffers allocated
Bind#3
oacdty=01 mxl=32(05) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=01 siz=0 off=80
No bind buffers allocated
Bind#4
oacdty=01 mxl=4001(4001) mxlc=00 mal=00 scl=00 pre=00  <<<<<<<< !!! only a long bind variable
oacflg=03 fl2=1000010 frm=01 csi=01 siz=4000 off=0
No bind buffers allocated

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.