Forum Stats

  • 3,781,410 Users
  • 2,254,514 Discussions
  • 7,879,681 Comments

Discussions

SQLException: protocol violation [14,114,] with specific nclob value

1553843
1553843 Member Posts: 2

please see attached test code and test data. with the given test data, JDBC throws protocol violation.

Server version, where this was found first: 11.2.0.4

fails with ojdbc7 version 12.1.0.1.0

fails with ojdbc6 version 12.1.0.1.0

works with ojdbc6 version 11.2.0.4

any hints/comments?

thanks in advance.

Exception:

java.sql.SQLException: Protocol violation: [ 14, 114, ]

at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:669)

at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)

at oracle.jdbc.driver.T4C8TTIClob.read(T4C8TTIClob.java:245)

at oracle.jdbc.driver.T4CConnection.getChars(T4CConnection.java:3901)

at oracle.sql.CLOB.getChars(CLOB.java:517)

at oracle.sql.CLOB.getSubString(CLOB.java:354)

at oracle.jdbc.driver.ClobAccessor.getString(ClobAccessor.java:454)

at oracle.jdbc.driver.GeneratedStatement.getString(GeneratedStatement.java:327)

at oracle.jdbc.driver.GeneratedScrollableResultSet.getString(GeneratedScrollableResultSet.java:882)

at OracleProtocolViolation.test(OracleProtocolViolation.java:43)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)

at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)

at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)

at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)

at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)

at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)

at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)

at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)

at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)

at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)

at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)

at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)

at org.junit.runners.ParentRunner.run(ParentRunner.java:309)

at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)

at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

Test code to reproduce:

import static org.junit.Assert.assertEquals;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import oracle.jdbc.pool.OracleDataSource;

import org.junit.Test;

public class OracleProtocolViolation {

  @Test

  public void test() throws SQLException {

    String url = "jdbc:oracle:thin:@<host>:1521:orcl";

    OracleDataSource oracleDataSource = new OracleDataSource();

    oracleDataSource.setURL(url);

    oracleDataSource.setUser("USER");

    oracleDataSource.setPassword("PASSWORD");

    Connection connection = oracleDataSource.getConnection();

    try (Statement stmt = connection.createStatement()) {

      try {

      stmt.execute("CREATE TABLE TABLE1 ( COLUMN1 NCLOB )");

      } catch (SQLException e) {

        stmt.execute("DELETE FROM TABLE1");

      }

    }

    String text = "<p><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"><span><strong>Testziel:</strong></span></span></p><p><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"><span></span><span style=\"font-family: 'Tahoma','sans-serif'; background: #fcfcfc; font-size: 8.5pt\">Schaltfl&auml;che </span><span>Auskunft - Zahldaten - Noch nicht ausgezahlte Kleinbetr&auml;ge anzeigen</span><span style=\"font-family: 'Tahoma','sans-serif'; background: #fcfcfc; font-size: 8.5pt\">... aktiv</span><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"></span><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"><strong> </strong></span></span></p><p><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"></span><span><strong>Beschreibung:</strong></span></span></p><p><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"><span></span><span style=\"font-family: 'Tahoma','sans-serif'; background: #fcfcfc; font-size: 8.5pt\">vorliegende Benutzerberechtigung -&gt; Feststellen</span></span></p><p><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"><span style=\"font-family: 'Tahoma','sans-serif'; background: #fcfcfc; font-size: 8.5pt\"></span></span><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"><span style=\"font-family: 'Tahoma','sans-serif'; background: #fcfcfc; font-size: 8.5pt\"></span></span><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"><span style=\"font-family: 'Tahoma','sans-serif'; background: #fcfcfc; font-size: 8.5pt\"></span><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"></span><span style=\"font-family: 'Tahoma','sans-serif'; background: #fcfcfc; font-size: 8.5pt\">Schaltfl&auml;che ist aktiv</span><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"></span><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"><strong> </strong></span></span></p><p><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"></span><span><strong>Vorbedingungen</strong></span></span><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"><span><strong>:</strong></span></span></p><p><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"><span></span><span style=\"font-family: 'Tahoma','sans-serif'; background: #fcfcfc; font-size: 8.5pt\">Benutzerberechtigung -&gt; Feststellen</span></span></p><p><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"><span style=\"font-family: 'Tahoma','sans-serif'; background: #fcfcfc; font-size: 8.5pt\"></span></span><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"></span><span><strong>Durchzuf&uuml;hrende Aktionen:</strong></span></span></p><p><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"><span></span><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\">Pr&uuml;fung der Schaltfl&auml;che</span><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"><strong> </strong></span></span></p><p><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"></span><span><strong>Erwartetes Ergebnis:</strong></span></span></p><p><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"><span></span><span style=\"font-family: 'Tahoma','sans-serif'; background: #fcfcfc; font-size: 8.5pt\">Schaltfl&auml;che ist aktiv</span><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"></span><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"><strong> </strong></span></span></p><p><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"></span><span><strong>Nachbedingung:</strong></span></span></p><p><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\"><span></span><span style=\"font-family: 'Tahoma','sans-serif'; font-size: 8.5pt\">keine</span></span></p>";

    try (PreparedStatement stmt = connection.prepareStatement("INSERT INTO TABLE1 (COLUMN1) VALUES (?)")) {

      stmt.setString(1, text);

      int insertedRows = stmt.executeUpdate();

      assertEquals(1, insertedRows);

    }

    try (PreparedStatement stmt = connection.prepareStatement("SELECT * FROM TABLE1")) {

      try (ResultSet rs = stmt.executeQuery()) {

        while (rs.next()) {

          System.out.println(rs.getString(1));

        }

      }

    }

  }

}

Tagged:

Answers

  • Something is odd. I tried 12.1.0.1.0 and12.1.0.2.0 drivers and could not duplicate the problem. My DBMS is 11.2.0.2.0...

  • Unknown
    edited Apr 29, 2014 5:08PM
    please see attached test code and test data. with the given test data, JDBC throws protocol violation.
    
    Server version, where this was found first: 11.2.0.4
    
    fails with ojdbc7 version 12.1.0.1.0
    fails with ojdbc6 version 12.1.0.1.0
    works with ojdbc6 version 11.2.0.4
     

    Ok - your problem statement is this: you have a 'bunch' of code and somewhere in that code you get an exception?

    any hints/comments?

    Yes - start troubleshooting your code to find out WHERE the exception is being thrown.

    There is NO exception handling anywhere in your code. Add some so that you know which statement is causing the exception.

    Also - explain this code:

    try (Statement stmt = connection.createStatement()) {
          try {
          stmt.execute("CREATE TABLE TABLE1 ( COLUMN1 NCLOB )");
          } catch (SQLException e) {
            stmt.execute("DELETE FROM TABLE1");
          }
        }
     

    That basically says:

    1. Try to create a table

    2. If you can't create the table try to delete ALL of the rows from the table that you could not create to begin with

    You appear to be assuming that the ONLY reason the CREATE TABLE would fail is:

    1. The table named 'TABLE1' already exists

    2. The existing TABLE1 table has EXACTLY the structure that the rest of your code requires.

    There are a lot of assumptions being made in that code. Perhaps you have an existing table named TABLE1 that doesn't have an NCLOB column at all but a VARCHAR2 column.

    I suggest that you fix the code so that it does NOT rely on assumptions like that and add exception handling so that when an exception occurs you know EXACTLY where it occurs.

    Once you have done that you can begin troubleshooting.

    Two tests you can try:

    1. Use a string that is less than 4000 bytes and see if the problem still exists. That current string is over 4200 characters which could be much longer in bytes depending on the database character set.

    2. Use the traditional method of populating and reading LOB columns; that is - first create and fetch the lob locator and then use streams for reading/writing the data.

    Yes - I know that 'getString' and 'setString' support was added in 11g:

    In Oracle Database 11g release 1 (11.1), the setBytes, setBinaryStream, setString, setCharacterStream, and setAsciiStream methods of PreparedStatement are extended for BLOB and CLOB parameters.

    But we are troubleshooting. So using the tried and true access methods can help rule out certain problems (e.g. values larger than 4000 bytes).

    See the JDBC Dev Guide example code and test using the methods shown there for using the lob locator and streams

    http://docs.oracle.com/cd/B28359_01/java.111/b31224/oralob.htm#sthref776

    Reading and Writing BLOB, CLOB and NCLOB Data

    A default LOB column uses a default CHUNK size of one Oracle block, typically 8k. You also typically provide a storage clause for LOB columns that define not only chunk size but whether to store the LOB 'in row' or out of row. And only the first 4000 bytes or so (bytes, not characters) can be stored in-line.

    So your TABLE definition is also lacking. Anytime you try to blaze your own trail instead of using 'best practices' you can expect to have problems. On a job site I wouldn't even spend time trying to troubleshoot your 'problem' until you brought the table definition and the Java code up to minimum standards. There are just too many side-effects that can complicate things.

    And when dealing with 'strings' and character lobs the character sets being used on the client and server can have an impact.

    So post you database character set and the character set being used on the client where the tests are performed.

  • Amit Yatagiri
    Amit Yatagiri Member Posts: 3
    edited May 6, 2014 5:55AM

    Same issue even I'm facing.

    ojdbc7, oracle thin client, Oracle 11g, java7, hibernate 4.2

    with ojdbc6 it was working fine. But when upgraded to ojdbc7 I'm getting protocol violations.

    When the clob length is more than 4000 I get the exception. But works fine when the data in clob is < 4000 characters.

    Please let me know if there is any patch or work around.

    For clob.getCharacterStream method, if the amount of data passed is <= 4000 then it works. But this iterative logic has to be present in the driver level.

    Surprisingly while inserting data into table ,it doesn't always (not consistent) restrict data size to be 4000.

  • issue is when length is 4193.. ufff..

  • gimbal2
    gimbal2 Member Posts: 11,949 Gold Trophy
    Amit Yatagiri wrote:
    
    issue is when length is 4193.. ufff..
    

    Specifically? So it doesn't break if the length of the text is 4192 or 4194? Or it breaks if it is over 4193?

  • Please don't HIJACK another users thread.

    Create your own thread if you have an issue or problem and post your own particulars in that thread as OP did for this thread.

    But works fine when the data in clob is < 4000 characters.  
    . . .
    Surprisingly while inserting data into table ,it doesn't always (not consistent) restrict data size to be 4000.
    
    

    Before you create your new thread make sure you are NOT confusion 'bytes' with 'characters'. Review what I first said above:

    1. Use a string that is less than 4000 bytes and see if the problem still exists. That current string is over 4200 characters which could be much longer in bytes depending on the database character set.
    
  • Amit Yatagiri
    Amit Yatagiri Member Posts: 3
    edited May 14, 2014 3:49AM

    Yes only for 4193 characters..

    Work around to try is, to increase the buffer size for clob fetch in the datasource properties.

    But anyways,this issue got resolved in the latest patch from oracle for ojdbc7.

  • gimbal2
    gimbal2 Member Posts: 11,949 Gold Trophy

    Ah good, thanks for providing that feedback!

  • 1553843
    1553843 Member Posts: 2

    Hello,

    good news.

    Which patch for ojdbc7 do you refer to? Where can I download the fixed version?

    thanks for all replies here.

This discussion has been closed.