Oracle7 Server Reference Manual
Specifying Character Sets
The character encoding scheme used by the database is defined at database creation as part of the CREATE DATABASE statement. All data columns of type CHAR, VARCHAR2, and LONG, including columns in the data dictionary, have their data stored in the database character set. In addition, the choice of character set determines which characters can name objects in the database.
Once the database is created, the database character set cannot be changed without re-creating the database. Hence, it is important to consider carefully which character set to use. The database character set should always be a superset or equivalent of the operating system's native character set. The character sets used by client applications that access the database will usually determine which superset is the best choice.
If all client applications use the same character set, then this is the normal choice for the database character set. When client applications use different character sets, the database character set should be a superset (or equivalent) of all the client character sets. This will ensure that every character is represented when converting from a client character set to the database character set.
When a client application operates with a terminal that uses a different character set, then the client application's characters must be converted to the database character set, and vice versa. This conversion is performed automatically, and is transparent to the client application. The character set used by the client application is defined by the NLS_LANG parameter.
Supported Character Sets
Oracle7 Server National Language Support features solve the problems that result from the fact that different encoding schemes use different binary values to represent the same character. With NLS, data created with one encoding scheme can be correctly processed and displayed on a system that uses a different encoding scheme.
Character Set Conversion
Where a character exists in both source and destination character sets, conversion presents no problem. However, data conversion has to accommodate characters that do not exist in the destination character set. In such cases, replacement characters are used. The source character is replaced by a character that does exist in the destination character set.
Replacement characters may be defined for specific characters as part of a character set definition. Where a specific replacement character is not defined, a default replacement character is used. To avoid the use of replacement characters when converting from client to database character set, the latter should be a superset (or equivalent) of all the client character sets.
The Concatenation Operator
If the database character set replaces the vertical bar ("|") with a national character, then all SQL statements that use the concatenation operator (ASCII 124) will fail. For example, creating a procedure will fail because it generates a recursive SQL statement that uses concatenation. When you use a 7-bit replacement character set such as D7DEC, F7DEC, or SF7ASCII for the database character set, then the national character which replaces the vertical bar is not allowed in object names because the vertical bar is interpreted as the concatenation operator.
On the user side, a 7-bit replacement character set can be used if the database character set is the same or compatible, that is, if both character sets replace the vertical bar with the same national character.
Storing Data in Multi-Byte Character Sets
Width specifications of the character datatypes CHAR and VARCHAR2 refer to bytes, not characters. Hence, the specification CHAR(20) in a table definition allows 20 bytes for storing character data.
If the database character set is single byte, the number of characters and number of bytes will be the same. If the database character set is multi-byte, there will in general be no such correspondence. A character may consist of one or more bytes, depending on the specific multi-byte encoding scheme and whether shift-in/shift-out control codes are present. Hence, column widths must be chosen with care to allow for the maximum possible number of bytes for a given number of characters.
Loadable Character Sets
Oracle7 Server release 7.3 loads character sets upon first reference. Instead of linking all character sets as static data, each character set is read into dynamic memory upon first reference. The size of the executable is thus reduced by eliminating character set data not in use during execution.