Handling Multibyte Characters

When the Oracle database is created, the database charset is set to UTF-8. In addition to the UTF-8 charset, the TIBCO MDM database needs to be deployed with a configuration for character semantics that enable globalization and enhance code portability.

From Oracle9i onwards, you can set the default character semantics at either the session or instance level using the NLS_LENGTH_SEMANTICS parameter. This parameter must be effective before a table is created.

The NLS_LENGTH_SEMANTICS parameter decides how strings should be stored in the database, as characters or bytes. TIBCO recommends that strings in the database be stored as characters (CHAR). This setting takes care of globalization issues with strings.

  • For a New TIBCO MDM Installation

    The database object creation scripts specify the correct semantics. If you use these scripts, you do not have to perform any additional steps.

  • For an Existing TIBCO MDM Installation

    Prior to TIBCO MDM 7.1, TIBCO MDM instances were created using the seed data export dump provided. Depending on the database NLS_LENGTH_SEMANTICS, the tables may not have been created with the correct semantics to handle NLS characters. You can convert the older instances to the correct semantics as follows:

    To convert an existing schema from byte semantics to character semantics and a single-byte character set to a multibyte character set:

Procedure

  1. Export the schema.
  2. Set the NLS_LENGTH_SEMANTICS parameter using either of the following methods:
    • In the init.ora file, set NLS_LENGTH_SEMANTICS=CHAR.
    • Issue the following command on the target database (This command modifies the NLS_INSTANCE_PARAMETERS and NLS_SESSION_PARAMETERS views. Storage is now measured in characters and not bytes):

      ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR SCOPE=BOTH

      Note: This resolves defects associated with Data too long for the Column when multibyte characters are used.
  3. Stop and restart the database so that the parameter change takes effect.
  4. Drop the original schema.
  5. Recreate the original schema and its tables. You can use the IMPORT SHOW=Y option to get the CREATE TABLE statements. Columns in the recreated tables will now use character semantics (as it is the default). The import command generates the create table statements.
  6. Run the create table statements and the schema is created.
  7. Import the schema into the target database using the "IGNORE=Y" IMPORT option.
  8. Export the dump and save it.
  9. Retest the dump as follows:

    alter system set nls_length_semantics=byte;

  10. Import the dump.

Result

The tables should have correct semantics even when NLS_LENGTH_SEMENTICS is set to byte.