Tips for character set conversion in Oracle databases

We all know that in a large database management system, Oracle databases play an obvious role in both database management capabilities and security. Chinese enterprises also use many Oracle databases, but the character sets of different Oracle versions bring a lot of trouble to data display, data backup, data conversion, and other practical work.

1. character set parameters

Once a database is created, its character set cannot be changed. Therefore, it is very important to consider which character set to use. The Oracle database character set should be a superset of the local character set of the operating system. The character set used by the customers who access the database determines which superset to choose, that is, the database character set should be the superset of all customer character sets.

The following describes some NLS_LANG parameters related to character sets:

NLS_LANG format:

 
 

  
  
NLS_LANG=language_territory.charset  
 
 

Has three components: language, region, and character set. Each component controls the NLS subset. Three components can be combined at any time, for example:

 
 

  
  
AMERICAN_AMERICA.US7SCII   
  
  
JPANESE_JAPAN.JA16EUC  
 
 

Language indicates the language of the server message.

Territory specifies the date and number format of the server.

Charset

You can also define the NLS_LANG parameter in some subsets:

The default date format of NLS_DATE_FORMAT

Default date language of NLS_DATE_LANGUAGE

The NLS_NUMBERIC_CHARACTERS decimal character is separated from the component

NLS_CURRENCY local currency character

NLS_ISO_CURRENCY ISO currency character

NLS_SORT character sorting Sequence

Ii. Character Set Conversion

1. How to modify the NLS_LANG parameter:

1) log on to Oracle with the SYS user name.

2) view character set content

 
 

  
  
SQL>SELECT * FROM PROPS$;  
 
 

3) modify the corresponding character subset

 
 

  
  
SQL>UPDATE PROPS$ SET VALUE$=’SIMPLIFIED CHINESE ‘   
  
  
WHERE NAME=’NLS_LANGUAGE’;  
 
 

4) Submit the ticket;

2. Specific applications of the NLS_LANG parameter:

1) The server/client mode is used. The character sets at both ends are different.

Modify the client character set:

WIN95/WIN98: Modify the Registry

 
 

  
  
HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\NLS_LANG  
 
 

UNIX: Follow the 1 method to directly go to Oracle for modification.

2) use EXP/IMP commands for different character sets

Follow the 1 method to modify the character set settings of IMP. If it is a WIN98/WIN95 system, you must also modify the registry. Note: The parameters of the three subsets of NLS_LANG must be consistent. In addition, if the single-and double-byte character sets are different, you cannot modify the character set for conversion. You can use other methods to perform Oracle database migration without modifying the character set. For example, if the data volume is small, you can use the SQLLOAD command to convert text files and use other Oracle database software ACCESS and FOXPRO) conversion.