Supported Oracle datatypes

The Oracle database interfaces support the Oracle datatypes listed in Table 9-2 in DataWindow objects:

Table 9-2: Supported datatypes for Oracle

Bfile

NChar (Oracle9i and later only)

Blob

Number

Char

NVarChar2 (Oracle9i and later only)

Clob

Raw

Date

TimeStamp (Oracle9i and later only)

Float

VarChar

Long

VarChar2

LongRaw

The Oracle 10g interface also supports BINARY_FLOAT and BINARY_DOUBLE datatypes. These are IEEE floating-point types that pass the work of performing floating-point computations to the operating system, providing greater efficiency for large computations.

Accessing Unicode data

Using the O90 or O10 database interface, DataWindow Designer can connect, save, and retrieve data in both ANSI/DBCS and Unicode databases, but it does not convert data between Unicode and ANSI/DBCS. When character data or command text is sent to the database, DataWindow Designer sends a Unicode string. The driver must guarantee that the data is saved as Unicode data correctly. When DataWindow Designer retrieves character data, it assumes the data is Unicode.

Using the O84 database interface, DataWindow Designer detects whether the Oracle client variable NLS_LANG is set. If the variable is set to a value that requires UTF-8 or DBCS characters, DataWindow Designer converts command text (such as SELECT * FROM emp) to the appropriate character set before sending the command to the database. However, if DisableBind is set to 0 (the default), DataWindow Designer always binds string data as Unicode data. Using O84, you can set the DisableUnicode database parameter to 1 to retrieve data as an ANSI string.

A Unicode database is a database whose character set is set to a Unicode format, such as UTF-8, UTF-16, UCS-2, or UCS-4. All data must be in Unicode format, and any data saved to the database must be converted to Unicode data implicitly or explicitly.

A database that uses ANSI (or DBCS) as its character set might use special datatypes to store Unicode data. These datatypes are NCHAR and NVARCHAR2. Columns with this datatype can store only Unicode data. Any data saved into such a column must be converted to Unicode explicitly. This conversion must be handled by the database server or client.

A constant string is regarded as a char type by Oracle and its character set is NLS_CHARACTERSET. However, if the datatype in the database is NCHAR and its character set is NLS_NCHAR_CHARACTERSET, Oracle performs a conversion from NLS_CHARACTERSET to NLS_NCHAR_CHARACTERSET. This can cause loss of data. For example, if NLS_CHARACTERSET is WE8ISO8859P1 and NLS_NCHAR_CHARACTERSET is UTF8, when the Unicode data is mapped to WE8ISO8859P1, the Unicode data is corrupted.

By default, the O90 and O10 database interfaces bind all string data to internal variables as the Oracle CHAR datatype to avoid downgrading performance. To ensure that NCHAR and NVARCHAR2 columns are handled as such on the server, set the NCharBind database parameter to 1 to have the O90 and O10 drivers bind string data as the Oracle NCHAR datatype.

If an Oracle stored procedure has an NCHAR or NVARCHAR2 input parameter and the input data is a Unicode string, set the BindSPInput database parameter to 1 to force the Oracle database to bind the input data. The O90 and O10 database interfaces are able to describe the procedure to determine its parameters, therefore you do not need to set the NCharBind database parameter.

For a DataWindow object to access NCHAR and NVARCHAR2 columns and retrieve data correctly, set both DisableBind and StaticBind to 0. Setting StaticBind to 0 ensures that DataWindow Designer gets an accurate datatype before retrieving.

TimeStamp datatype

The TimeStamp datatype in Oracle9i and later is an extension of the Date datatype. It stores the year, month, and day of the Date value plus hours, minutes, and seconds:

Timestamp[fractional_seconds_precision]

The fractional_seconds_precision value is optional and provides the number of digits for indicating seconds. The range of valid values for use with DataWindow Designer is 0-6.