Monday, 23 July 2007

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.

3 comments:

Anonymous said...

Since your DB was created with AL32UTF8 , you need to declare your column as VARCHAR2(4000 CHAR), instead of VARCHAR2(4000) which really means VARCHAR2(4000 BYTE)

Anonymous said...

The anonymous suggestion above won't work. VARCHAR columns are still limited to 4,000 bytes in Oracle. A VARCHAR(4000 CHAR) column on an AL32UTF8 database will still be 4,000 bytes, and unable to store, e.g., more than 1,333 characters of Chinese text.

Anonymous said...

The suggestion will work. When the parameter nls_length_semantics=CHAR is set beforehand, created tables will have the VARCHAR2(## CHAR) attribute for varchar columns and length will be calculated by character count rather than byte count. Thus a varchar2(4000 CHAR) column would be able to hold a 4000 character AL32UTF8 string even if each character required 4 bytes for a total of 16,000 bytes.