Unicode support with Oracle

ODBC Driver Version

• Unicode support is only available when using v8.1.5.5 of the Oracle ODBC Driver or later
• Unicode support is not available when using the Microsoft ODBC Driver for Oracle

Data Types

If your database is using a Unicode character set, char, varchar2 and clob data types will hold Unicode values.

Irrespective of your database character set nchar, nvarchar2 and nclob data types will hold Unicode values.

Unicode Character Set Database

Full support of unicode in your Oracle database is only available if your Database Character Set has been set to a unicode character set, such as UTF8. The database character set is specified when your database is defined.

You can find out the database character set with:

Select value from SYS.NLS_DATABASE_PARAMETERS where PARAMETER = 'NLS_CHARACTERSET'

ASCII Character Set Database

If your Oracle database has been defined to use an ascii character set (as opposed to a unicode character set), then you can still store unicode data in your Oracle database. However there are some limitations on what you can do.

1. you can display unicode data OK
2. you cannot run an SQL statement containing a unicode literal
3. you cannot supply a unicode value to a parameter in a parameterised query.

The reason for these limitations is that, when you pass SQL or a parameter to the database, it will convert the values to the character set of the database. If the database uses an Ascii character set, the unicode values will be converted to Ascii characters.

AQT has a circumvention for the point number 2 - this is discussed below.

Beause the Data Loader uses parameterised queries by default, it cannot load unicode values unless you use the Load using Insert Statements option.

Unistr Function

Oracle has a function UNISTR which allows you to specify unicode character values. This allows you to insert or update unicode values in your database, even if your databases uses an Ascii character set.

The UNISTR function is not available in Oracle v8.

Running SQL Statements with Unicode literals

If your database uses a unicode character set, you can run SQL statements that include unicode literals.

If your database uses an Ascii character set, you cannot run SQL statements that include unicode literals. To circumvent this, AQT has an option Unicode Options > For Oracle, use UNISTR for Unicode strings. When this is option is checked, AQT will scan all SQL being processed. If it finds any unicode string values, it will replace them with a unistr function:

Example:

if you run the following:

Select * from HR.TEST1 WHERE COL1 = 'ald'

AQT will actually run:

Select * from HR.TEST1 WHERE COL1 = unistr('\03B1\03BB\03B4')

This makes it very easy to use unicode data in an Oracle database that uses an Ascii characterset.