Oracle Database character set research

Oracle Server:

Select * from NLS_database_PARAMETERS where parameter like '% CHARACTERSET % ';
PARAMETER VALUE
----------------------------------------------------------------------
NLS_CHARACTERSET WE8MSWIN1252
NLS_NCHAR_CHARACTERSET AL16UTF16

Table t1:

Desc t1;
Name Null Type
----------------------
ID NUMBER
NAME NVARCHAR2 (100)

SQL Developer query and insert Chinese

C: \ sqldeveloper \ bin \ sqldeveloper. conf

AddVMOption-Doracle. jdbc. convertNcharLiterals = true

Preference --> Environment, Encoding select UTF-16

Alter session set NLS_LANGUAGE = 'simplified CHINESE ';
Truncate table T1;
Insert into T1 VALUES (1, n' Ah ');
Insert into t1 values (2, 'Ah ');
COMMIT;
Col name format a10;
Select * from t1;

ID NAME
--------------------
1 ah

2 cores ¿

N'ah' indicates nvarchar data.

The following content has not been tested:

JDBC Configuration

-Doracle. jdbc. defaultNChar = true
-Doracle. jdbc. convertNcharLiterals = true

SQLPlus in Linux

NLS_LANG = AMERICAN_AMERICA.AL32UTF8
LC_CTYPE = "zh_CN.utf8"
ORA_NCHAR_LITERAL_REPLACE = true

Varchar2 Length

The length of a field of the varchar2 type is calculated by characters or bytes, such as varchar2 (10 bytes) and varchar2 (10 char ). In general, byte/char are ignored. At this time, NLS_LENGTH_SEMANTICS defines the default value.

Show parameters NLS_LENGTH_SEMANTICS;
NAME TYPE VALUE
-----------------------------------
Nls_length_semantics string BYTE

Character Set of the database

Select * from nls_database_parameters where parameter like '% CHARACTER %'
PARAMETER VALUE
------------------------------------
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16

The character length of AL32UTF8 encoding is variable, 1-4 bytes, and ASCII character is 1 byte. As follows:

Desc t2;
Name Null Type
--------------------
TNAME VARCHAR2 (2)

Select * from t2;
TNAME
-----
12

It can be seen that numbers 1 and 2 each occupy one byte.

View the number of bytes of a character in the Current Character Set

Select lengthb ('C'), lengthb ('Ah'), length ('\ R') from dual;
LENGTHB ('C') LENGTHB ('Ah') LENGTH ('\ R ')
------------------------------------
1 3 2