Oracle varchar2 character storage Length

Problem description:

Oracle source table can store"Chinese test"Four Chinese characters, but after being extracted and written to the target table of the same structure by powercenter, the Chinese characters are truncated to two Chinese characters"Chinese".

Create Table source table

(

 AVarchar2 (4 char)

)

 

Create Table target table

(

 AVarchar2 (4 char)

)

 

Solution:

This problem is causedNls_length_semantics = byteAnd must be setChar.

 

For exampleIntegrationService add environment variables:

Name: nls_length_semantics

Value: Char

 

System Environment:
Operating System: Window Server 2003 sp2
Middleware: WebSphere platform 6.1
Database: Oracle 10.2.0.1.0
Character Set: nls_characterset utf8
Maximo version: V7110-890

Symptom:
1. When you execute similar statements during the migration of Maximo database configuration information:
Insert into wfassignment @ orcl57 select * From wfassignment t where T. processname = pc_row.processname and T. processrev = pc_row.processrev;
An exception is always reported: the actual length of description is 106, and the length of Description field in the target table is 100, which exceeds the maximum length.

2. Check the result field in the target: wfassignment. Description varchar2 (100). Only about 33 Chinese characters can be inserted with one English text;
A Chinese Text occupies three bytes, and an English text occupies one byte.
In the original database wfassignment. Description, 100 Chinese characters can be inserted.

3. Use lengthb (description) and length (description) to query the length of a field,
If the field values in the original database are all Chinese characters, the query result lengthb is three times the length,
If the same field value is in the new database, the query results are of the same length.

Cause analysis:
1. The database character set is nls_characterset = utf8. This is not the case if nls_characterset = zhs16gbk;

2. The original library contains nls_length_semantics = char, and the new library contains nls_length_semantics = byte;

3. This is mainly caused by 2nd characters. If the character set is utf8 and one character in field type varchar2 occupies several bytes, it is determined by the nls_length_semantics parameter,
If it is byte, one character occupies one byte. If it is Char, one character occupies four bytes.
The following statement shows the actual length of data_length.
Select table_name, column_name, data_type, data_length from Cols

4. The parameter table for viewing the character set parameters of the database is V $ nls_parameters. parameters related to the character set are in this table;
You can also use the following statement to query;
Select * from V $ parameter where name = 'nls _ length_semantics'
National Character Set: nls_nchar_characterset = al16utf16
Database Character Set: nls_characterset = al32utf8

Solution:
1. Modify the "nls_length_semantics" attribute value of the target database to "char"

1. Execute: Alter system set nls_length_semantics = char scope = both;
After this method is modified and the database instance is restarted, the query value is byte;
2. Modify the file: {installpath}/Oracle/product/10.2.0/admin/orcl/pfile/init. ora.622010124846
Add the following section:
######################################## ###
# NLS
######################################## ###
Nls_language = "simplified Chinese"
Nls_length_semantics = char
Nls_territory = "China"

After this method is modified and the database instance is restarted, the query value is byte;
3. Restart the server computer. Then, check that the value is Char. The reason is not found.

2. The field description still cannot insert 100 Chinese Characters
1. Although nls_length_semantics has been changed to Char, when the table field description varchar2 (100) in the target database is still
Cannot insert 100 Chinese Characters

2. query the Cols table and check whether the length of data_length is 100. Check wfassignment. Description again.
Field definition object. The database type changed to varchar2 (100 byte)

3. alter table wfassignment modify description varchar2 (100); problem solving;
Query the Cols table and find that the length of data_length is 400. you can insert 100 Chinese characters.

4. Execute the following statement, save the generated script as an SQL file, and use the PL/SQL tool to update all character fields:
Select 'alter table' | table_name | 'modify' | column_name | 'varchar2 ('| data_length | ');'
From Cols where data_type = 'varchar2' and table_name in
(Select table_name from tabs where status = 'valid ')

3. In Maximo products, because the wfassignment. Description varchar2 (100) field is a full-text retrieval field,
You cannot directly modify the length of a field. You must delete the full-text search index. For details, see delete all full-text search indexes in Maximo.

 

 

Reproduced http://blog.csdn.net/lanyunit/article/details/5768581

 

Select * from V $ nls_parameters

Update props $ set nls_characterset = 'utf8'
The client's nls_lang is also set to utf8

 

Http://www.eygle.com/index-special.htm ORACLE data recovery, etc.