Omnis Technical Note TNSQ0023 August 2008
Mixing Unicode and Non-Unicode Data Types with Oracle
For Omnis Studio 5
By Gary Ashford
IntroductionThis 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
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
|$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 DataThe 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 dataTo 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 dataWhere 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