亡命天涯

metalink 上 有个解释,好像一个表如果有一个varchar2(4000)后, 如果还定义一个大于varchar2(1333)的column,那么在操作这个表时可能出这个错.

但我们伟大的developer 发现,只要你的pl/sql block 块里面有一个

大于varchar2(4000)的变量定义,并把它引用在DML语句中,oracle 在parse 时候就会报错,怪吧? 解决办法是DML之前就已经赋值到一个小于varchar2(4000)长度的变量里,问题解决,版本8.1.7.3,不知道其他版本会不会有类似问题,这个.. 算不算bug???

下面附上metalink 里ora-01461 解决方案供以后参考

Subject: Workarounds for bug 1400539: GETTING ORA-1461 INSERTING INTO A VARCHAR
Doc ID: Note:241358.1 Type: BULLETIN
Last Revision Date: 10-JUL-2006 Status: PUBLISHED

In this Document
Purpose
Scope and Application
Workarounds for bug 1400539: GETTING ORA-1461 INSERTING INTO A VARCHAR
References

_____

Applies to:

Oracle Server - Enterprise Edition - Version:
Information in this document applies to any platform.

Purpose

This note tries will describe reasons and workarounds for Bug 1400539. 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 and Application

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.

Workarounds for bug 1400539: GETTING ORA-1461 INSERTING INTO A VARCHAR

Background
A character from the client character set (for example WE8ISO8859P1), can take up to 3 bytes of storage in the database character set UTF8 (we will focus on UTF8 here, but the same logic applies for other multi-byte character sets). That means that when you try to use this table from the client, the conversion ratio is set to 1:3. When connecting to a UTF8 server, then all character lengths are multiplied by 3 since this is the maximum length that the data could take up on the server.The maximum size of a VARCHAR2 is 4000 bytes. Anything bigger will be treated as a LONG. During run-time no check is made for the actual content of the columns. Even if a VARCHAR2(2000) column only contains 1 character, this is treated as if you're using a LONG (just like a LONG that contains only 1 character). If you have 1 of these columns plus a LONG, or simply 2 or more of these columns, effectively the database believes that you are binding 2 long columns. Since that is not allowed you receive this error.

The influence of NLS_LENGTH_SEMANTICS
NLS_LENGTH_SEMANTICS allows you to either set the size of columns in bytes or in characters, although internally most calculations are still based on bytes. If you define a column to be "VARCHAR2(20 CHAR)", we take the maximum length
that any character can be, and multiply the length by that. That way we get the maximum amount of bytes for that column. In UTF8 the maximum number of bytes for a character is 3. If you check DBA_TAB_COLUMNS you will see "DATA_LENGTH" is 60 for a "VARCHAR2(20 CHAR)" column. On top of that we also store the maximum number of characters (20) in the "CHAR_LENGTH" column. The logic of how you run into this bug applies to the DATA_LENGTH of a column, not the CHAR_LENGTH. So if you define the strings in terms of characters and define a column of "VARCHAR2(445 CHAR)", the DATA_LENGTH of that column will be 1335 and you might run into this problem (only if you have a LONG or another column like this in the same table of course). So where we talk about a maximum of 1333 bytes in this note, you could also read that as a maximum of 444 characters (in UTF8) if you choose to define lengths in characters.

Workarounds:
If you run into this problem there are 4 workarounds:

1. Limit the size of the buffer from within the OCI code
When you hit this problem in a OCI application you have control over, you should be able to work around it by setting the OCI_ATTR_MAXDATA_SIZE attribute of the bind variable to set the maximum size of the buffer that is used to 4000 bytes. That way there is a guarantee that the buffer will never exceed the size for a VARCHAR2, and therefore this problem cannot occur. You can set this attribute using a call to OCIAttrSet after you've done the bind (for example with OCIBindByName)

For example:

int maxdata_size = 4000;

...
OCIBindByName(stmthp1, &bnd1p, errhp, (oratext*)":ENAME",
(sb4) strlen((char *)":ENAME"), (void *) ename, sizeof(ename),
SQLT_STR, (void *)&insname_ind, (ub2 *) 0, (ub2 *) 0, (ub4) 0,
(ub4 *)0, OCI_DEFAULT);
OCIAttrSet((void *) bnd1p, (ub4) OCI_HTYPE_BIND, (void *) &maxdata_size, (ub4) 0,
(ub4) OCI_ATTR_MAXDATA_SIZE, errhp);
...

As you see in the example, after the bind statement we set the OCI_ATTR_MAXDATA_SIZE attribute of the bind variable to be "4000" (which is what maxdata_size is set to in the example). This means on that on the server side we will still treat this as a VARCHAR2 because the size does not exceed the maximum for that type.

2. Use the database character set also as the client character set This is possible for Web applications or other applications that can use Unicode/UTF8. If you application runs in the OS character set (ISO/ANSI/DEC/...) you cannot set the NLS_LANG to UTF8 unless the OS Locale itself runs in UTF-8. If you run on a Unix system, depending on what precise version and vendor, it might be possible to change the Unix Locale to UTF-8. Please see section 4 of Note 158577.1 for more information and links about that: If your application cannot use UTF8 then the best option is to use workaround 1 or 3.

3. Decrease the size of the columns
If you make sure that there is only 1 LONG and no VARCHAR > 1333 bytes, OR just 1 VARCHAR > 1333 bytes in the table, you cannot hit this problem. The following query will give you all the tables with such a combination of
columns:

SELECT * FROM
(SELECT TABLE_NAME, OWNER, count(*) NUM
FROM DBA_TAB_COLUMNS
WHERE DATA_TYPE='LONG'
OR (( DATA_TYPE='VARCHAR2'
or DATA_TYPE='CHAR'
or DATA_TYPE='NVARCHAR2'
or DATA_TYPE='NCHAR')
AND DATA_LENGTH > 1333)
AND OWNER NOT IN
('SYS','SYSTEM','SH','OLAPSYS','MDSYS','WKSYS','ODM','XDB','WMSYS')
GROUP BY TABLE_NAME, OWNER)
WHERE NUM > 1;
(by default this statement filters out the standard schema's that should not contain any user data, you can change that if needed)

These tables will have to be looked at. You need to decide if you can decrease the size of the (VAR)CHARs to 1333 bytes or less. Because you should hit this bug before production (in either development or testing) it should not take to long to determine if this is something that can be done and it should certainly not take too long to implement if it's decided to do so.

If you run into this problem because you have defined strings bigger than "VARCHAR2(444 CHAR)" then in most cases you should be able to redefine them as "VARCHAR2(1333 BYTE)" without loosing much of the lengths of the strings you can store. In practice it will not happen a lot that all characters you store in a string will use the full 3 bytes of storage. So, for example, if you tried to define a "VARCHAR2(1000 CHAR)" you could run into this problem. If you change that to "VARCHAR2(1333 BYTE)" you will in practice still be able to store around 1000 characters (for languages based on ASCII).

4. Do not use the multibyte character set as the database character set This is usually not a option if you hit this issue on JA16SJIS or JA16EUC, because there are no single-byte alternatives for this. However, if you store European or Arabic characters in a (AL32)UTF8 database and hit this problem then it could be an alternative to use a single byte character set. However, it is still better and usually easier to use one of the earlier mentioned workarounds than it is to go back to a single byte character set. Certainly if you hit this problem when you're developing a new application then the other 3 workarounds are the preferred option and it should certainly be possible to use either of those. In a scenario where you're changing character sets from an existing database and where the application does not change dramatically it could be difficult to use workaround 1 or 2. In that case workaround 3 should certainly be investigated.
Something to keep in mind is that if you're changing to a UTF8 database you can really only make full use of the new possibilities if your client also starts to use a Unicode character set. For example if you have a ANSI application that always runs on Western European Windows machines, changing to UTF8 storage wouldn't add any extra characters because the application can't handle them anyway. So changing the application into Unicode and using workaround 1 would really be the best option.

Fixes
In Oracle8i and Oracle9i this is a limitation for which you need to use one of the workarounds. This problem is 'fixed' in Oracle 10i. Because of the change in architecture that is required, this behavior will not change for Oracle8i and Oracle9i.

References

Bug 1400539 - Getting Ora-1461 When Inserting Into A Varchar Field Bug 1538990 - Ora-1461 When Inserting And Updating To Db(Sjis) From Client(Euc) Bug 2909286 - Ora-1461 Generated On Insert In Proc To A Utf-8 Database Bug 2998709 - Ora-01461: Can Bind A Long Value Only For Insert Into A Long Column Note 158577.1 - NLS_LANG Explained (How does Client-Server Character Conversion Work?) Note 179133.1 - The correct NLS_LANG in a Windows Environment Note 225912.1 - Changing the Database Character Set - a short overview Note 101578.1 - Example: OCI 8i Example on UCS2-encoding Note 223025.1 - Truncation of multi-byte characters within an OCI application. Note 60134.1 - Globalization (NLS) - Frequently Asked Questions Note 267942.1 - Globalization Technology (NLS) Knowledge Browser Product Page

Errors

ORA-1461 <http://metalink.oracle.com/metalink/plsql/ml2_gui.handleSearchRequest?p_search=Submit&p_text=ORA-1461> can bind a LONG value only for insert into a LONG column

Key Facts

'MULTIBYTE <http://metalink.oracle.com/metalink/plsql/ml2_gui.handleSearchRequest?p_search=Submit&p_text='KEYWORD:MULTIBYTE'> ' 'AL32UTF8 <http://metalink.oracle.com/metalink/plsql/ml2_gui.handleSearchRequest?p_search=Submit&p_text='KEYWORD:AL32UTF8'> ' 'CHARACTER <http://metalink.oracle.com/metalink/plsql/ml2_gui.handleSearchRequest?p_search=Submit&p_text='KEYWORD:CHARACTER'> ' 'CONVERSIONRATIO <http://metalink.oracle.com/metalink/plsql/ml2_gui.handleSearchRequest?p_search=Submit&p_text='KEYWORD:CONVERSIONRATIO'> ' 'NLS_LANG <http://metalink.oracle.com/metalink/plsql/ml2_gui.handleSearchRequest?p_search=Submit&p_text='KEYWORD:NLS_LANG'> ' 'ORA-1461 <http://metalink.oracle.com/metalink/plsql/ml2_gui.handleSearchRequest?p_search=Submit&p_text='KEYWORD:ORA-1461'> '