Character Set combination determines the length of varchar2-Oracle defines the length of varchar2 () type to store Chinese Characters

Why can Oracle varchar2 (4000) connect with the JDBC thin driver only store 666 Chinese characters?

Who says they can only store 666 Chinese characters?

Varchar2 is a maximum of 4000 bytes, So it depends on your Oracle character set. If the character set is 16-bit encoded, zhs16gbk, then each character is 16-bit, 2-byte, so it can accommodate 2000 characters.
If it is a 32-bit character set, only 1000 characters can be stored.

Select length ('ours '), lengthb ('ours') from dual;

 

Character Set combination determines the length of varchar2

Problem description:
Intert into t_board values ('more than 17 Chinese characters ');
Error: The inserted character is too long! An error is reported because one Chinese Character occupies three bytes !!!

Problem:
This error may occur if the character set used is utf8!
Run the following command to View Details:

SQL> select parameter, value from nls_database_parameters where parameter like 'nls _ characterset ';

Parameter
------------------------------------------------------------
Value
--------------------------------------------------------------------------------
Nls_characterset
Al32utf8

Solution:
We recommend that you use the zhs16gbk character set!
Operation:
SQL> shutdown immediate;
SQL> startup Mount;
SQL> alter system enable restricted session;
SQL> alter system set job_queue_processes = 0;
SQL> alter database open;
SQL> alter database character set al32utf8/zhs16gbk;

SQL> shutdown immediate;

SQL> startup;

Solve the problem !!! (Note that the new character set is the old superset when changing)

Note 1:

Re: Study: Why does my Oracle data operation suddenly report a number limit error )?	Posting time:
This is a classic oraclejdbc issue. Generally, when you directly use ps. setstring () to set string data, the oracle JDBC driver will convert Chinese characters to 2 or 3 bytes, which is not fixed, so it will often cross the border. If you change to PS. setcharacterstream (), it is fixed to two bytes for each Chinese character.

According to Oracle documents, the thin JDBC driver determines the varchar2 Length Based on the character set combination.
Or the Latin character set combination, the length limit is 2000, because it thinks that other character sets require two bytes for storage, but when using the JDBC setstring method, the driver will
The UTF-16 of Java is converted to UTF-8, so that English is changed from two bytes to one byte, and Chinese is changed from two bytes to three bytes, so 2000/3 is about 666 Chinese characters.

NOTE 2:

When performing the update operation, the method is as follows:
Pstmt. setbytes (4, aimstring. getbytes (); When retrieving data:
New String (resultset. getbytes ("fieldname"), "ISO-8859-1 ");