ORA-12899 is too large for the column despite the same length

Question

I run the following query. But get ORA-12899. Although the length of the line I'm trying to insert is 30.

INSERT INTO TABLE1 SELECT * FROM temp_Table1 where LENGTH(column1)=30;

SQL Error: ORA-12899:value too large for column "TABLE1"."column1" (actual: 31, maximum: 30)


select column1 from temp_Table1 where LENGTH(column1)=30;

Testing  - HLC/TC Design Corre

Desc temp_Table1

column1 VARCHAR2(30)

Desc Table1

column1 VARCHAR2(30)

Answer 1

You see the difference between the character and the semantics of the byte length :

VARCHAR2. 1 , (''). CHAR, VARCHAR2 (10 CHAR), . . BYTE, VARCHAR2 (10 BYTE), . , NLS_LENGTH_SEMANTICS , .

, :

select value from nls_session_parameters where parameter = 'NLS_LENGTH_SEMANTICS';

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

create table t42(text varchar2(5));

Table T42 created.

select char_used from user_tab_columns where table_name = 'T42' and column_name = 'TEXT';

C
-
B

, :

create table t42(text varchar2(5 byte));

, , :

insert into t42 (text) values ('Hello');

1 row inserted.

insert into t42 (text) values ('Señor');

SQL Error: ORA-12899: value too large for column "SCHEMA"."T42"."TEXT" (actual: 6, maximum: 5)

, . , , length() , . lengthb(), . 30- , , , 31 , .

with t42 (text) as (
  select 'Hello' from dual
  union all select 'Señor' from dual
  union all select 'Testing  - HLC/TC Design Corre' from dual
)
select text, length(text) as chars, lengthb(text) as bytes, dump(text, 16) as hex
from t42;

TEXT                            CHARS BYTES HEX                                                                                                      
------------------------------- ----- ----- ----------------------------------------------------------------------------------------------------------
Hello                               5     5 Typ=1 Len=5: 48,65,6c,6c,6f                                                                               
Señor                               5     6 Typ=1 Len=6: 53,65,c3,b1,6f,72                                                                            
Testing  - HLC/TC Design Corre     30    31 Typ=1 Len=31: 54,65,73,74,69,6e,67,c2,a0,20,2d,20,48,4c,43,2f,54,43,20,44,65,73,69,67,6e,20,43,6f,72,72,65

dump() , Testing (54,65,73,74,69,6e,67) (20,2d) c2,a0, UTF-8. ( , , ASCII, , , , Word).

LENGTHB(column1)=30 ( ), 30 30 :

drop table t42;

Table T42 dropped.

create table t42(text varchar2(5 char));

Table T42 created.

select char_used from user_tab_columns where table_name = 'T42' and column_name = 'TEXT';

C
-
C

insert into t42 (text) values ('Hello');

1 row inserted.

insert into t42 (text) values ('Señor');

1 row inserted.

, ; , , .

Answer 2

,

ALTER TABLE1 MODIFY column1 VARCHAR2(30 CHAR)

, 1 30 , 30 , .

: Oracle - VARCHAR2 CHAR

Answer 3

-12899

, , , , , , . , Oracle ​​ . , . . ORA-12899 , Oracle .

ORA-12899 - Oracle, , . , . , , , . , . , , . . , , . , ?

OERR. ORA-12899 . . , . SQL, . , . . , width - - , , . , ORA-12899. , :

CREATE TABLE Clients(
ClientID varchar2(9) PRIMARY KEY,
Client_Contact varchar2(40) NOT NULL,
Address varchar(20) NOT NULL,
Zip varchar2(5) NOT NULL,
Client_Number varchar2(11) NOT NULL)

INSERT VALUES, - :

INSERT INTO Clients VALUES(
727546345,
‘Roger Holcomb’,
‘—Benjamin Road Syracuse’,
‘-----‘, 05307623754)

, :

, 7 : INSERT INTO (727546345, " ", 18 , '13208, 05307623754) : SQL: ORA-12899: "". "". "" (: 25, : 20) 12899. 00000 - % s (: % s, :% s) "

, 'Address , . (18 Road Syracuse), , (25) . , VARCHAR2 , , .

URL-

Answer 4

- NLS .   alter table1 column1 varchar2 (30 char)