B Column Length Semantics and Unicode

Column Length Semantics for Replication Sites and Table Columns

Column length semantics determine whether the length of a column is specified in bytes or in characters. You use BYTE to specify that the length is in bytes, and you use CHAR to specify that the length is in characters. CHAR length semantics is also known as codepoint length semantics.

Because some character sets require more than one byte for each character, a specification of 10 BYTE for a column might actually store less than 10 characters for certain character sets, but a 10 CHAR specification ensures that the column can store 10 characters, regardless of the character set. Only Oracle9i Database or later databases can specify CHAR length semantics.

You set the length semantics for an Oracle database using the NLS_LENGTH_SEMANTICS initialization parameter, and all VARCHAR2 and CHAR columns use the setting specified for this initialization parameter as the default. If this initialization parameter is not set, then the default setting is BYTE.

An individual column can override the length semantics for the database. For example, if the length semantics for a site is CHAR, then you can still specify BYTE for the length semantics of an individual column using the CREATE TABLE or ALTER TABLE statement.

The following statement creates a table and specifies the column length in bytes:

CREATE TABLE byte_col (a VARCHAR2(10 BYTE));

The following statement creates a table and specifies the column length in characters:

CREATE TABLE char_col (a VARCHAR2(10 CHAR));

Multimaster Support for Column Length Semantics

All master sites in a master group must have the same length semantics, and the individual columns of a master table must have the same length semantics at all master sites. When you have a table in a master group at a master definition site and you want to replicate that table to a new master site, you can create the table at the new site in one of the following ways:

• Specify that Advanced Replication generate the table at the new master site when adding the new master site to the master group.

• Manually precreate the table at the new master site before adding the master site to the master group.

The following sections describe column length semantics support for each table creation method.

Materialized View Support for Column Length Semantics

When you create a materialized view, Oracle determines the length semantics of the columns in the materialized view in the following way:

• If the master column is explicitly specified as either BYTE or CHAR, then the column in the materialized view retains that specification. In the following example, CHAR length semantics is explicitly specified for the a column:

  CREATE TABLE char_col (a VARCHAR2(10 CHAR));
  

• If the master column is not explicitly specified, then the column in the materialized view uses the default length semantics of the materialized view site. In the following example, length semantics is not explicitly specified for the a column:

  CREATE TABLE char_col (a VARCHAR2(10));
  

Materialized view creation fails if an Oracle9i Database or later master has a column with an explicit CHAR specification and a materialized view site running a release prior to Oracle9i Database attempts to create a materialized view based on this master.

DDL Propagation and Column Length Semantics

You can use the DBMS_REPCAT package to propagate a data definition language (DDL) statement that creates a new replicated table or adds columns to an existing replicated table. If you want any of the new columns created by these DDL statements to use CHAR column length semantics, then ensure that you specify CHAR column length semantics explicitly. Otherwise, the column always has BYTE length semantics, even if the replication site itself has CHAR column length semantics set as the default.

The following procedures in the DBMS_REPCAT package enable you to propagate DDL statements:

• ALTER_MASTER_REPOBJECT

• CREATE_MASTER_REPOBJECT

• EXECUTE_DDL

Replication Support for Unicode

Unicode is a universal encoded character set that enables you to store information from any language using a single character set. Unicode provides a unique code value for every character, regardless of the platform, program, or language. Unicode is supported in both multimaster and materialized view replication environments. In Oracle9i Database or later, all columns specified as NCHAR or NVARCHAR2 data type are stored in Unicode format.

For both master sites and materialized view sites, replication is possible in an environment with different releases of Oracle using an NCHAR or NVARCHAR2 data type. However, replication is not recommended when one of the replication sites is a release prior to Oracle9i Database and uses a variable width character set because, in this case, there is a possibility of data loss.

Table B-4 summarizes when replication is recommended.

In an Advanced Replication environment involving Oracle Database 10g or later sites and sites running Oracle9i Database that use NCHAR and NVARCHAR2 data types, an Oracle patch must be installed at the sites running Oracle Database releases prior to Oracle9i Database Release 2 (9.2). Contact Oracle Support Services to obtain the appropriate NLS patch as recommended in ALERT 140014.1, "Oracle8/8i to Oracle9i using New AL16UTF16 National Character Set" available on the Oracle MetaLink Web site.