RelationalDBDesign RelationalDBDesign

Language Support   «Prev  Next»
Lesson 5National Character Sets
ObjectiveChoose a National Character Set for a Database

Two types of Character Sets

An Oracle database has two types of character sets: the database character set and the national character set. The database character set is used to determine what types of data can be used for identifiers, PL/SQL programs, and the data stored in CHAR, VARCHAR2, CLOB, and LONG columns. The national character set is used to store and interpret the data kept in NCHAR and NVARCHAR2 columns. These two data types are specifically designed to accept national language characters.
You can set the national character set with the NLS_NCHAR parameter. Like the NLS_LANG parameter, the NLS_NCHAR parameter is set in the client environment.
By using the NLS_CHAR setting, you can use a single Oracle database to store more than one type of character string in the same database. For instance, you may want to store both Chinese and English characters in your Oracle database. You would have to specify a database character set that could handle Chinese characters, but you might want to store all of this type of data in NCHAR or NVARCHAR2 columns. Some database character sets also support multiple national character sets, so you could use the same method to support a widely mixed user community.
The following SlideShow illustrates the relationship between the database character set and the national character set and the CHAR/VARCHAR2 data types and the NCHAR/NVARCHAR2 data types:

  1. Database character set defines the character set for the entire database
  2. Any data stored in columns with a datatype of CHAR, VARCHAR2, CLOB and LONG can use any characters in the database character set
  3. The national character set defines the character set for the the national language setting for a particular user
  4. Any data stored in columns with a datatype of CHAR, VARCHAR2, or NCLOB uses the national character set .

Database Character Set


Although Oracle does support multiple-byte character sets, a few types of data must always be implemented with a single-byte character set. Database names, instance names, filenames, and rollback segment names can be written only with single-byte characters, whereas keywords must always be in English. There is also a difference between how you specify length for the CHAR/VARCHAR2 data types and for the NCHAR/NVARCHAR2 data types. The CHAR/VARCHAR2 data types are typically used for single-byte character sets, so the length specification for these data types is in terms of bytes. For fixed-length multibyte character sets, the length of the NCHAR/NVARCHAR2 data types refers to the number of characters in the column.

Creating your own character set

Oracle gives you the ability to create your customized character sets by extending an existing character set. You may need to do this to include a variety of information, such as vendor-specific codes. The NLS Data Installation Utility can be used to create these extended character sets. The next lesson will explain some of the ways that you can convert data from one language to another.