ORA-01461 from JDBC program trying to insert large String

Having a database column defined as VARCHAR2(4000) in a table and trying to insert a character string which was 3983 characters failed with a runtime error as follows in a 10.2.0.2 Oracle Database with the character set AL32UTF8.

java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into
a LONG column

What is odd is that when run against the same database version 10.2.0.2 with a character set as WE8ISO5589P1, it worked fine no errors at all. The issue is that the when using character set as WE8ISO8859P1 it will be inserted without loss. But if the database is in the character set AL32UTF8 the Latin 1 characters will be expanded and the total size will increase.

Eg: http://www.unicode.org/charts/PDF/U0080.pdf

A simple Java class illustrates this as follows:

package project1;

public class Test
{
public static String test =
  "No lions. No tigers, but bears …";

static void showEncodedLength(String s, String encoding)
  throws Exception
{
  byte[] b = s.getBytes(encoding);
  System.out.println(encoding + " byte length: " + b.length);
}

public static void main(String[] args)
  throws Exception
{
  System.out.println("String length: " + Test.test.length());
  showEncodedLength(Test.test, "ASCII");
  showEncodedLength(Test.test, "ISO8859_1" );
  showEncodedLength(Test.test, "UTF8");
}
}

When run the output is as follows showing that the length increases when using UTF8 encoding even though only just this will result in a runtime error when trying to insert into large columns such as VARCHAR2(4000). The error ORA-01461 itself is not very useful, and fails to indicate the real issue here. From the output below you can see that it requires more length to insert into a UTF8 database

Output

String length: 32
ASCII byte length: 32
ISO8859_1 byte length: 32
UTF8 byte length: 34

If you have such an issue you should consider using a CLOB column if the data may exceed the maximum size of a varchar2 column as was the case here.