Skip to main content

Oracle and Varchar2 size problem

I recently ran into a problem with Oracle version 9i. I was trying to insert a value into a varchar2(3200) column when I got the below exception.

javax.servlet.ServletException: java.sql.SQLException: ORA-01461: canbind a LONG value only for insert into a LONG column

The size of the text that I was trying to insert was actually lesser than the limit of 3200 characters. Yet Oracle thought the value that was being inserted was too large for the column. After reading some forums and articles, I found out that the problem was due to encoding of the characters. When content is saved, the saved sized of the content is actually more than the actual size of the content due to some data encoding conversion (to UTF8). This probably depends on the encoding of your database. You can run the following command to find your database's encoding.

select * from nls_database_parameters where parameter='NLS_CHARACTERSET'

If your database's encoding is set to UTF8, you should see something very similar to AL32UTF8.

One way to solve this problem is to convert your varchar2 column into clob (binary) column type. This way you would not need to worry about size restrictions. If this is not possible, you could try increasing the size of the varchar2 column while keeping a validation at a lower character limit. One thing to remember is that Oracle varchar2 has a limit of 4000 characters.

Comments

ismail said…
I also recently found out that setting oracle.jdbc.RetainV9LongBindBehavior the property to true on 10g JDBC drivers seems to solve this problem.
Nawaz Ijaz said…
Hi Ismail,
we are facing similar problem in our production environment. However we could not generate this problem in local environment. Our database encoding is NLS_CHARACTERSET = AL32UTF8
NLS_LENGTH_SEMANTICS = CHAR
NLS_RDBMS_VERSION = 10.2.0.3.0
JDBC Driver: 10.2.0.2.0

Our JSP fields allow 1000 characters to be inserted in Varchar2(4000) fields. So even if a full length String of 1000 characters of 4-byte each is entered by a user it should be inserted into the database without any problem. Could you please tell us what might be the reason of getting this error (ORA-01461:Can bind a LONG value only for insert into a LONG column)?
Nawaz Ijaz said…
Hello Ismail, actually i am unable to generate this problem in our local environment. How can i get this exception in my local environment?

I have already tried with the following setups but could not get this exception at all in my local environment..

NLS_CHARACTERSET = AL32UTF8
NLS_LENGTH_SEMANTICS = CHAR/BYTE
NLS_RDBMS_VERSION = 10.2.0.3.0
JDBC Driver: 10.2.0.1.0/10.2.0.2.0

/ Nawaz Ijaz
ismail said…
Hello,

I found out there were few reasons for this error to happen. One reason is due to an encoding mismatch between the client and the server set up. If there is a mismatch, Oracle drivers seems to be converting characters to UTF-8. You may want to check your JVMs encoding and verify that it is also set to UTF-8.

I also found out that there seems to be an open issue with Oracle regarding this error. The below is a reference to the issue.

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/readme_10201.html

I could not find the status of it though.

As I mentioned we ended up solving the problem by adding the driver property oracle.jdbc.RetainV9LongBindBehavior to our db pool definition.

I hope this helps.
ismail said…
The link above seems to be cut in half. Use the one below.

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/readme_10201.html
ismail said…
It did it again. Click below for oracle page.

Oracle and Varchar2 Problem