0

On our customer's database the following charset is set:

NLS_CHARACTERSET    AL32UTF8

On my local database the following is configured:

NLS_CHARACTERSET    AL32UTF8

When an insert into a VARCHAR2(4000) column is performed on our customer's database this error occurrs:

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

On my computer everything is working fine.

The value is bind using a java.sql.PreparedStatement with preparedStatement.setString(3, value3);.

Could it be that the error is due to a difference in the charset? Does the charset affect the amount of characters that can be stored in a VARCHAR2 field?

Harold L. Brown
  • 8,423
  • 11
  • 57
  • 109
  • Looks like the error happens on a `LONG` column (which is deprecated for decades) rather than a `VARCHAR2` column. – Wernfried Domscheit Jul 07 '15 at 12:47
  • 3
    You are trying to insert a Long value into a column of different type. Could you double check that everything is the same on your local and the remote computer. I don't think it has anything to do with the encoding. – Florian Jul 07 '15 at 12:50
  • @Wernfried When calling `DESC table_name` the column is displayed as `VARCHAR2(4000)`. – Harold L. Brown Jul 07 '15 at 12:53
  • Can we see the actual code, in context? You may think the problem is the line you cited, when it's actually due to something you did five lines earlier... it looks like you are binding at least three variables in your SQL statement, and it may be one of the others causing the problem. And we don't know what `value3` is declared as, either. – dcsohl Jul 07 '15 at 13:38

1 Answers1

0

Prior to Oracle 12.1, a VARCHAR2 column is limited to storing 4000 bytes of data in the database character set even if it is declared VARCHAR2(4000 CHAR). Since every character in your string requires 2 bytes of storage in the UTF-8 character set, you won't be able to store more than 2000 characters in the column. Of course, that number will change if some of your characters actually require just 1 byte of storage or if some of them require more than 2 bytes of storage.

See also this answer.

Community
  • 1
  • 1
Harold L. Brown
  • 8,423
  • 11
  • 57
  • 109
  • 1
    Not sure you can assume that "every character in your string requires 2 bytes of storage in the UTF-8 character set". ASCII characters only require 1 character; Chinese characters will require 3 or 4. – dcsohl Jul 07 '15 at 13:31