需要理解使用NLS_LENGTH_SEMANTICS的Oracle语言

Question

我使用以下方法在Oracle12c中创建DataBase和表：

CREATE TABLE t
(a VARCHAR2(14 CHAR))

如果我想说:MARIE-TH R

INSERT INTO t (a) values ('MARIE-THÉRÈSE');

ERROR at line 1:
ORA-12899: value too large for column "T"."A" (actual: 15, maximum:
14)

一些资料：

    SQL> SELECT LENGTH('MARIE-THÉRÈSE') "Length in characters" FROM DUAL;

Length in characters
--------------------
                  15
SQL> SELECT LENGTHB ('MARIE-THÉRÈSE') "Length in bytes" FROM DUAL;

Length in bytes
---------------
             23
SELECT *
   FROM nls_database_parameters
   WHERE parameter = 'NLS_CHARACTERSET';

PARAMETER                      VALUE
------------------------------ --------------------
NLS_CHARACTERSET               AL32UTF8

SELECT value FROM NLS_DATABASE_PARAMETER
WHERE parameter='NLS_LENGTH_SEMANTICS';

VALUE
-----------------------------------
BYTE

怎样才能不修改这一列，而只修改甲骨文参数，就能把‘MARIE-TH R_(VARCHAR2)’写进本专栏(14 CHAR)？

Answer 1

解决办法是：

VARCHAR2(20) : Uses the default length semantics defined by the NLS_LENGTH_SEMANTICS parameter which defaults to BYTE.
VARCHAR2(20 BYTE) : Allows only the specified number of bytes to be stored in the column, regardless of how many characters this represents.
VARCHAR2(20 CHAR) : Allows the specified number of characters to be stored in the column regardless of the number of bytes this equates to.

SQL> CREATE TABLE tab2 (
  2    id           NUMBER(10),
  3    description  VARCHAR2(20 CHAR)
  4  );
Table created.

SQL> DESC tab2
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- -----------------
 ID                                                             NUMBER(10)
 DESCRIPTION                                                    VARCHAR2(20 CHAR)

可以使用NLS_LENGTH_SEMANTICS参数更改数据库或会话的默认字符语义。

ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR;
ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=BYTE;

ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;
ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;

如果我们将会话长度语义更改为字符，并描述表，我们可以看到，通过列定义的显示方式，更改已经受到了影响。

SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;

Session altered.

SQL> desc tab1
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- -----------------
 ID                                                             NUMBER(10)
 DESCRIPTION                                                    VARCHAR2(20 BYTE)

SQL> DESC tab2
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- -----------------
 ID                                                             NUMBER(10)
 DESCRIPTION                                                    VARCHAR2(20)

SQL>

INSTR、LENGTH和SUBSTR函数总是处理字符，而不考虑列定义和字符集。当您特别需要处理字节时，Oracle提供了INSTRB、LENGTHB和SUBSTRB函数。

Answer 2

如果没有在DDL中指定长度语义参数，则定义默认使用的内容。例如，如果你说

CREATE TABLE T
( a VARCHAR2(14) )

然后，长度语义参数将确定列A是14个字节还是14个字符。

因此，在您的示例中，您有一个15个字符字符串，因此无论长度语义如何，您都不能将它插入到14个字符列中；您需要修改列定义。

ALTER TABLE T
MODIFY a VARCHAR2(15 CHAR)