Kbase P119314: Oracle error 1461 inserting records into a table with more than one VARCHAR2 fields bigger than 1333
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
08/09/2008 |
|
Status: Verified
SYMPTOM(s):
Oracle error 1461 inserting records into a table
ORA-01461: can bind a LONG value only for insert into a LONG column
Trying to insert a VARCHAR2 into a column that is defined with a length of more than 1333 bytes.
The table either has another LONG column or at least 1 other VARCHAR2 with a length over 1333 bytes.
Oracle Database character set is set a multibyte character set. For example UTF8, AL32UTF8, JA16SJIS or JA16EUC.
Using Oracle 8 or 9i
FACT(s) (Environment):
Oracle DataServer
All Supported Operating Systems
CAUSE:
Oracle Bug 1400539. Problem is related to using a single byte client character set and a multibyte database character set.
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.
FIX:
There are three possible workarounds:
1. Use the database character set also as the client character set
2. 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.
3. Do not use the multibyte character set as the database character set
4. Upgrade to Oracle 10. In Oracle8i and Oracle9i this is a limitation for which you need to use one of the above 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.