Developers

Omnis Technical Note TNSQ0023 August 2008

Mixing Unicode and Non-Unicode Data Types with Oracle

For Omnis Studio 5
By Gary Ashford

Introduction

This Tech Note summarises the recent changes made to the Unicode Oracle Object DAM which enable insertion and retrieval of mixed ANSI and Unicode character types.
In the case of Oracle 8i and later, these data types are:

CHAR	Fixed single-byte character data, limited to 2000 bytes.
NCHAR	Fixed multi-byte character data, limited to 2000 bytes. (1000 UCS-2 encoded characters)
VARCHAR2	Varying length, single-byte character data, limited to 4000 bytes.
NVARCHAR2	Varying length, multi-byte character data, limited to 4000 bytes. (2000 UCS-2 encoded characters)
CLOB	Character Large Object- single-byte character data
NCLOB	National Character Large Object- multi-byte character data
LONG	Varying length, single-byte character data, limited to 2GB. Supported for backward compatibility only.

  

By default, the Unicode Oracle DAM maps all Omnis character data to the NVARCHAR2 and NCLOB data types, dependent on the field length of the Omnis bind variable. However, the Oracle DAM provides session properties which affect the Omnis->Oracle data type mappings. These are summarised below:
  

$encoding	The character encoding size required by the Oracle National Character set. Defaults to UTF-16 / UCS-2.
$nationaltonvarchar	If set to kTrue, Character and National data types are treated differently when being inserted to VARCHAR2 / NVARCHAR2 columns.
$nationaltonclob	If set to kTrue, large Character and National data types are treated differently when being inserted to CLOB / NCLOB columns.
$maxvarchar2	Sets the byte limit above which Omnis character fields will be mapped to CLOB/NCLOB datatypes as opposed to VARCHAR2 / NVARCHAR2 columns. The maximum value is 4000 bytes.
$longchartoclob	If set to kTrue (the default), Omnis large character fields > $maxvarchar2 in byte length will be mapped to the CLOB/NCLOB datatype. If set to kFalse, the LONG datatype is used.

Reading Unicode and Non-Unicode Data

The Oracle DAM automatically detects the data type of retrieved character columns and converts the data accordingly.
There is no need to modify any properties in order to retrieve mixed ANSI and/or Unicode Data.

Insertion/Update of CHAR and VARCHAR2 data

To write short character data to ANSI columns it is necessary to set $nationaltonvarchar to kTrue. In this mode, Omnis Character fields will be mapped to VARCHAR2 and National fields will be mapped to NVARCHAR2.
When set to kFalse (the default), both Character and National types will be mapped to NVARCHAR2.

Insertion/Update of CLOB data

Where the Omnis field length exceeds $maxvarchar2, the DAM will map to either CLOB, NCLOB or LONG dependent on the value of the $nationaltonclob and $longchartoclob properties. To write long character data to ANSI CLOB columns, it is necessary to set $nationaltonclob to kTrue. In this mode, Omnis Character fields will be mapped to CLOB and National fields will be mapped to NCLOB. When set to kFalse (the default), both Character and National types with byte sizes exceeding $maxvarchar2 will be mapped to NCLOB.

Note that where Omnis fields are mapped to NCLOB columns, $maxvarchar2 is interpreted as the length in bytes. Thus when set to 4000, this mapping will be applied for Character and/or National fields with a field length > 2000 characters

Insertion/Update of LONG data

When $longchartoclob is set to kFalse, Omnis Character and National fields which would otherwise map to CLOB or NCLOB will be mapped to the LONG datatype. Since Oracle tables may contain only one column of type LONG, this may lead to problems if not used judiciously.

Database Encoding

It should not be necessary to change the value of the $encoding property for the Oracle DAM since the client API expects UTF-16 encoded values and parameters. Use of this property should be reserved for future enhancements.