Insert VARCHAR2 Field In proc report ORA-01461: canbindaLONGvalueonlyforin

I recently encountered such a strange problem when I was working on a project of tianyinda freight interface. Background: An application written using proc inserts records into the database. The table has three VARCHAR2 (4000) fields. Note: The database of Oracle9i. Problem: prompt when executing: ORA-01461: canbindaLONGvalueonlyforinsertintoaLONG

I recently encountered such a strange problem when I was working on a project of tianyinda freight interface. Background: An application written using proc inserts records into the database. The table has three VARCHAR2 (4000) fields. Note: Oracle 9i Database. Problem: prompt when executing: ORA-01461: can bind a LONG value only for insert into a LONG

I recently encountered such a strange problem when I was working on a project of tianyinda freight interface.

Background:

Use the application written In proc to insert records into the database. The table has three VARCHAR2 (4000) fields. Note: Oracle 9i Database.

Problem:

The prompt: ORA-01461: can bind a LONG value only for insert into a LONG column

Records cannot be inserted, but it is no problem to manually execute the same SQL statement using PLSQL Developer or SQLPLUS.

Then a 10 Gb library is replaced, and proc can be used to correctly complete the insertion.

Does 9i library have any special restrictions on proc insertion?

Solution Process:

1. query OERR's description of the problem:

With this problem in mind, OERR looks at the error 1461 without any explanation.

2. query MOS's description of the problem:

Next, check out MOS, Workarounds for bug 1400539: GETTING ORA-1461 inserting into a varchar (Document ID 241358.1). This article corresponds to this question.

This article describes the sum of 9i and earlier versions, and points out that errors of the same ORA may occur in later versions, but the root cause is different from the one described here. A bug (1400539) was developed and fixed in 10.1.0.1.

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

Problem symptoms

(1) Using PRO * C or OCI. (2) Database character set is set a multibyte character set. for example UTF8, AL32UTF8, JA16SJIS or JA16EUC. (3) Trying to insert a VARCHAR2 into a column that is defined with a length of more than 1333 bytes. (4) The same table either has another LONG column or at least 1 other VARCHAR2 with a length over 1333 bytes. (5) 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"

One of the reasons for this problem is that (1) PRO * C is used. For other possible causes:

(5). Environment Character Set of the proc application:

> Echo $ NLS_LANG

AMERICAN_AMERICA.ZHS16CGB231280

(2). view the database character set:

> SELECT * FROM nls_database_parameters;

NLS_CHARACTERSET ZHS16GBK

"When connecting to a UTF8 server, then all character lengths are multiplied by 3 since this is the maximum length that the data cocould 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, please tively the database believes that you are binding 2 long columns. since that is not allowed you receive this error."

This article introduces a scenario, that is, when connecting to the database of the UTF8 character set, the length of all characters must be multiplied by 3, because this is the space occupied by the data of this character set. The maximum length of the VARCHAR2 type is 4000 bytes. Any larger storage value is treated as LONG.

The actual content of the column is not checked during running. Even if the VARCHAR2 (2000) column contains only one character, it is processed as LONG, just like a LONG field containing one character. If there is one such column, plus a LONG column, or two or more such columns, the database will think that you are binding two LONG columns. Therefore, this error is reported.

For the workaround method with the preceding error, MOS provides four types:

1. Limit the size of the buffer from within the OCI code

2. Use the database character set also as the client character set

3. Decrease the size of the columns

4. Do not use the multibyte character set as the database character set

For my problems,

1. Here I am using a char array. It is estimated that it will be changed to the proc type of varchar to limit the character length, which is similar to the length of the OCI character, but it is due to energy and is not used.

2. This method is similar to the solution to the character set problem encountered during imp/exp export to avoid the problem caused by inconsistent character sets.

3. "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. ", if you confirm that this table has only one LONG type, no VARCHAR type greater than 1333 bytes, or only one VARCHAR type greater than 1333 bytes, you can bypass this problem. This depends on whether the business logic of the application matches the database design.

4. This is also an avoidance of the multiplication 3 problem caused by character sets.

The last method is to use 10.1.0.1 or a later version.

3. errors reported by PLSQL Developer, SQLPLUS, and proc are different.:

This problem was not encountered when using PLSQL Developer or SQLPLUS because they used a different driver from proc, and proc also used OCI to connect to the database, therefore, this is about Using PRO * C or OCI.

Lab:

Perform the following experiment for the above descriptions:

(1) Declare four variables a, B, c, and l In proc and assign the initial values:

Char a [4001], B [4001], c [4001];
Long l;

Memset (a, 0, sizeof ());
Memset (B, 0, sizeof (B ));
Memset (c, 0, sizeof (c ));

Strcpy (a, "");

L = 1;

Strcpy (B, "B ");

Strcpy (c, "c ");

(2) create a test table and insert records with proc:

Create table TBL_LV1
(
L long,
B VARCHAR2 (10 ),
C VARCHAR2 (10)
);

INSERT... L, B values (: l,: B );

Pluggable.

INSERT... L, B, c values (: l,: B,: c );

Error.

Create table TBL_LV1
(
L long,
B VARCHAR2 (10)
);

VARCHAR2 (1334), VARCHAR2 (4000)

INSERT... L, B values (: l,: B );

Pluggable.

Create table TBL_LV1
(
A VARCHAR2 (10 ),
B VARCHAR2 (10)
);

INSERT... A, B values (: a,: B );

Error.

However

INSERT... A, B values ('A', 'B'); no error is returned.

Even if it is changed:

Create table TBL_LV1
(
A VARCHAR2 (4000 ),
B VARCHAR2 (4000)
);

INSERT... A, B values ('A', 'B'); no error is reported.

Summary:

1. if you use proc to connect to the 9i library, the ORA-01461 will appear when you insert the VARCHAR2 type due to the multi-byte character problem between the client and the server: can bind a LONG value only for insert into a LONG column. However, if you use the PLSQL Developer or SQLPLUS non-OCI drivers, no error is reported.

2. Use proc to bind the variable, according to the experiment above, will make the error of ORA-01461 produce more confusion.

3. The above problems only occur in 9i and earlier versions, and bugs have been fixed in 10.1.0.1. If 9i and earlier versions are still used, Oracle provides the following four workaround types:

1. Limit the size of the buffer from within the OCI code (using the OCI driver to determine the buffer size (4000 ))

2. Use the database character set also as the client character set (the character set of the database and client is consistent)

3. Decrease the size of the columns (reduce the column length according to the character set length limit)

4. Do not use the multibyte character set as the database character set (Do not use the multi-byte character set as the database character set)