[SOLVED] Oracle not able to insert data into varchar2(4000 char) column

Oracle not able to insert data into varchar2(4000 char) column

I use Oracle 12c. I have below table in my DB.

CREATE TABLE TEST_T (COL VARCHAR2(4000 CHAR));

I need insert multibyte characters in this table. The character is 3 byte character.
I am able to insert only 1333 (upto 3999 bytes) characters in table. My expectation is to insert upto 1500 multibyte characters but I get ORA - 01461.
I don't want to change data type to CLOB or LONG. Is there any way to use VARCHAR2(4000 CHAR) to achieve this.

Below is the code,

SET SERVEROUTPUT ON
DECLARE
  LV_VAR CHAR(1):='プ';     -- 3 byte character
  LV_STR VARCHAR2(32000) := '';
BEGIN
  FOR I IN 1..1500
  LOOP
    LV_STR := LV_STR||LV_VAR;
  END LOOP;
--
  INSERT INTO TEST_T VALUES (LV_STR);
END;
/



Error report -
ORA-01461: can bind a LONG value only for insert into a LONG column
ORA-06512: at line 11
01461. 00000 -  "can bind a LONG value only for insert into a LONG column"
*Cause:    
*Action:

Solution

The problem is that the 4000 byte limit is a hard limit, regardless of whether the datatype is defined as VARCHAR2(4000 CHAR), VARCHAR2(4000 BYTE), or NVARCHAR2(4000). This means that multibyte characters will always have the chance of overflowing a max-size non-CLOB text column.

Oracle's table of Datatype Limits shows each of the VARCHAR2 variants as holding a max of 4000 bytes. And this is precisely the problem you have encountered.

You do have the option of increasing the max size for VARCHAR2 in your Oracle 12c database to 32k.

Here's how to do it: MAX_STRING_SIZE documentation

This is not something to be done without careful consideration: once you change your database to use extended VARCHAR2 strings you cannot go back. Nevertheless, if your database is all your own and you like the idea of having 32K strings, then this feature was created specifically to address your situation.

Be careful to read the details of pluggable databases, container databases as they require different upgrade techniques. This is a change that cuts across the entire database so you want to get it right.