SQLException: Data size bigger than max size for this type using 9i JDBC Driver

If your using the 9i JDBC Driver in this case the (9.2.0.8) driver and you try to update/insert a very large string , say 100,000 bytes into a LONG column you will get a runtime error as follows

java.sql.SQLException: Data size bigger than max size for this type: 1000000

There are 2 ways to get around this.

OPTION 1
-----------

Use CLOB to handle very large column data instead of using a LONG column. CLOB columns is the recommended way to deal with large column values, so avoid using LONG if possible.

OPTION 2
-----------

Until JDBC THIN Driver 10.1.x there is a restriction on the amount of data that can be used be with methods setBytes(), setString() for example.

When using a JDBC THIN Driver 9.x or 10.1.x you should use setBinaryStream() or setCharacterStream() instead. The 10.2.x driver will automatically switch to using streams if the data exceeds a certain value. So switching to the 10.2.0.4 JDBC driver for example will make it even easier to get around this.


Here is some code you can use to verify this with 9.2.0.8 JDBC driver then use the 10.2.0.4 driver and verify it will work in 10.2.0.4 JDBC Driver fine.

SQL Needed

drop table long_table;

create table long_table
(id number,
 long_col long)
/

insert into long_table values (1, 'Sample Data');

commit;

Java Code

import java.sql.*;

import oracle.jdbc.OracleDriver;

public class LongDemo
{
  public LongDemo()
  {
  }

  public void run () throws SQLException
  {
    Connection conn = getConnection();

     // print driver details
     // Create Oracle DatabaseMetaData object
     DatabaseMetaData meta = conn.getMetaData ();

     // gets driver info:

     System.out.println("\n=============\nDatabase Product Name is ... " +
     meta.getDatabaseProductName());
     System.out.println("Database Product Version is  " +
     meta.getDatabaseProductVersion());
     System.out.println("\n=============\nJDBC Driver Name is ........ " +
     meta.getDriverName());
     System.out.println("JDBC Driver Version is ..... " +
     meta.getDriverVersion());
     System.out.println("JDBC Driver Major Version is " +
     meta.getDriverMajorVersion());
     System.out.println("JDBC Driver Minor Version is " +
     meta.getDriverMinorVersion());
     System.out.println("=============");
    
    String updateQuery = "update long_table set long_col = ? where id = 1";
    PreparedStatement updateStatement = conn.prepareStatement(updateQuery);
    String outValue = createLargeString(1000000);
    System.out.println("Update String size: " + outValue.length());
    updateStatement.setString(1, outValue);
    updateStatement.executeUpdate();
    updateStatement.close();
   
    conn.commit();
    conn.close();
  }

  /*
   * Creates a large string the size we require
   */
  private String createLargeString(int size)
  {
    StringBuffer b = new StringBuffer(size);
    for (int i=0; i<size; i++)
    {
      b.append("X");
    }
    return b.toString();
  }
 
  public static Connection getConnection() throws SQLException
  {
    String username = "tars";
    String password = "tars";
    String thinConn = "jdbc:oracle:thin:@papicell-au2:1521:lnx102";
    DriverManager.registerDriver(new OracleDriver());
    Connection conn = DriverManager.getConnection(thinConn,username,password);
    conn.setAutoCommit(false);
    return conn;
  }
 
  public static void main(String[] args)
  {
    LongDemo longDemo = new LongDemo();
    try
    {
      longDemo.run();
      System.out.println("update worked fine");
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    }
  }
}