Database Character Set

Database character sets are used by Oracle for storing data in the data types CHAR, VARCHAR2, CLOB, and LONG (including the columns referenced in the data dictionary). It is the data set used for identifiers for things such as table names, index names, column names, and PL/SQL variable names, and for entering SQL commands and entering and storing PL/SQL source code. It is the character-encoding scheme used by the database that was defined in the CREATE DATABASE statement.

After the database is created, you cannot change the database character set without re-creating the database. It is therefore important that you consider the character set that you want to use. The following is a list of questions to consider when you are deciding what database character set to define at database creation time:

• What primary languages does the database need to support?

• What languages do you foresee the database needing to support in the future?

• Is the character set that you want to use available on the operating system (this speaks to ASCII versus EBCIDIC)?

• What character sets will be used on the majority of the clients?

• If you are implementing a specific application that this database will support, how well does the given application handle the character set?

• Are there any performance implications of the character set?

• Are there any significant restrictions to you or your company's business that can be associated with the character set?

• Are you going to need the ability to display special characters (such as S or b, m or p) that are to be stored in the database?

• Are there any interoperability concerns with system resources on the machine that will host the database or with any of the applications that will use it as a back end?

Several different character sets may meet the needs that are determined by the answers to questions such as these. But you should consider not only the needs that you have, as an organization, at the time that the database is built but also how those requirements might change in the future. Choosing the database character set with the widest range of possibilities and that still includes the requirements that you have when you are building the database is the best bet. It eliminates the need, down the road, for migration to a different character set.

Because the database character set is used to identify and store SQL and PL/SQL source code, it must have either an EBCIDIC or 7-bit ASCII as one of its subsets. This is because of the different architectures of the operating systems on which the Oracle database can reside. For this reason, it is not possible to have a fixed-width multibyte character set as a database character set, only as a national character set.

It is not possible to have any fixed-width multibyte character set as the database's database character set. Only the national character set can be a fixed-width multibyte set.

Although the database maintains and processes the actual database character set, you must depend on other resources from the operating system for your database and instance to function optimally.

The operating system supplies the fonts that correspond to the character set that you have chosen (both the client operating system and the server operating system, if these are different platforms). It is possible for Oracle to convert the operating system's character set into the database's character set (and vice versa). If you happen to choose a character set different from what is available on the operating system, you should remember that overhead goes along with this character set conversion. Make sure that the operating system has characters in its character set equivalent to any of the special characters that you might store in the database so that no data is lost in the conversion.

Performance implications can accompany the decision you make, too. For best performance, you should choose, if possible, a character set that will mean that no conversions are necessary at the operating system level. Single-byte character sets are more optimal for performance than are multibyte character sets (they are also the most efficient in terms of storage space requirements) but do limit the number of languages that you can use.

Now that we have looked at the database character sets and what those bring to your environment, let's look at an alternative to this character set, the National Character Set.