Out of Babylon

Oracle offers several possibilities for migrating to Unicode. However, some of them are omitted (i.e., the "Oracle Database Migration Utility for Unicode," or DMU for short, which Oracle has offered since 2011) in a production scenario, because enterprise requirements do not envisage the necessary downtime or because the system requirements for the product (release and patch status) are not met. In contrast, the procedure presented here migrates very large databases in a very short time.

Why Unicode?

Every database is created with a certain code page. En route from the application to the database, the characters in the data are translated: The application code page is mapped to that of the database. However, this is only the case if you have character data types (CHAR, VARCHAR2, CLOB, LONG, NCHAR, NVARCHAR2, NCLOB), because you do not want to convert binary data, of course.

Conversion takes place automatically in transit (via Oracle Net); for example, a Euro sign typed on a Windows machine (0x80 in WIN-1252) is entered in the database as 0xA4 – if you create the database with the ISO-8859P15 character set. However, if the database was created with a Unicode character set, then the Euro sign is stored as a three-byte sequence (0xE282AC) and therefore requires three times as much space as before.

In the past, most databases in Europe were created with a one-byte character set (i.e., ISO-8859 or MSWIN-1252).

In the course of globalization, companies now increasingly need global databases. Oracle has thus recommended for some time that enterprises use Unicode as the default character set. Many modern applications (e.g., Java applications) also use Unicode by default. The same applies to many of today's operating systems, such as Microsoft Windows 7 or Linux. However, when you're converting an Oracle database to Unicode, several difficulties can occur. I will discuss some of these issues and their solutions in this article.

Column Overrun

When designing tables, the column width must be defined as a certain number of characters, not bytes. Fields suitable for this purpose can be created with the CHAR data type:

CREATE TABLE status ( statusid CHAR(1),description VARCHAR2(50));

However, does the 1 here mean a length of one byte or one character? The Oracle database uses the parameter NLS_LENGTH_SEMANTICS for this definition, and it has to be set appropriately to char or byte. Because these semantics only became available in Oracle 9i, and previously only byte could be used, the length semantics are still set to byte for many older applications (or for applications that have been migrated across several Oracle releases.) In a Unicode migration, this can lead to a situation in which a record cannot be inserted, for example, because it contains a non-standard character, with the result:

ORA-02374: conversion error loading table "DEMO"."STATUS"
ORA-12899: value too large for column STATUSID (actual: 2, maximum: 1)

In this case, the status column contains "Ü" as the ID, but this is a two-byte character in Unicode.

In new applications, you would thus always explicitly state the length semantics. Old applications need to be modified in the course of the migration; you would make this change

CREATE TABLE status ( statusid CHAR(1 CHAR),description VARCHAR2(50 CHAR));

in the previous example.

Maximum Data Type Length

The Oracle documentation often states that the CHAR data type has a length of 2,000 characters and VARCHAR2 a length of 4,000 characters. But this is not true, because the maximum length is expected in bytes, that is, a VARCHAR2 field can be a maximum of 4,000 bytes long.

CREATE TABLE status ( statusid CHAR(1 CHAR),description VARCHAR2(4000 CHAR));

In this case, therefore, the length semantics are wrong, because the description field can actually only be 4,000 bytes. (Despite this, Oracle still allows what could be a misleading definition.)

If this field is filled up to the last character and contains non-standard characters, which are longer than one byte in Unicode, then the conversion will fail.

Instead, you need to convert the data type to LONG, or preferably CLOB. The table definition would then look like this:

CREATE TABLE status ( statusid CHAR(1 CHAR),description CLOB)LOB (description) STORE AS (ENABLE STORAGE IN ROW);

for the previous example.