Workarounds for bug 1400539: GETTING ORA-1461 INSERTING INTO A VARCHAR (Doc ID 241358.1)

Purpose

This note is applicable to 9i and lower. Note that the same ORA- error may indeed be seen in higher versions but then the root cause and conditions are different.

This note tries will describe reasons and workarounds for <Bug 1400539>. There was a extensive code rewrite done for 10.1 to eliminate the issue reported in this bug.

This bug describes the following problem:

Problem:
GETTING ORA-1461 WHEN INSERTING INTO A VARCHAR FIELD

Problem symptoms

• Using PRO*C or OCI.
• Database character set is set a multibyte character set. For example UTF8, AL32UTF8, JA16SJIS or JA16EUC.
• Trying to insert a VARCHAR2 into a column that is defined with a length of more than 1333 bytes.
• The same table either has another LONG column or at least 1 other VARCHAR2 with a length over 1333 bytes.
• NLS_LANG is set to a single-byte character set. For example american_america.WE8ISO8859P1

Resulting error
ORA-1461: "can bind a LONG value only for insert into a LONG column"

Scope

There are a number of ways to "hit" this bug, and some of the workarounds can be more or less relevant depending on the exact circumstances. However, in all cases the problem will be down to using a single byte client character set and a multibyte database character set. If that is not a setup you use then this is not a problem you have hit. If you indeed have a setup like that then there is a good chance that some of the workarounds given below will solve your problem.

Details

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.