Supported Oracle datatypes

The Oracle database interfaces support the Oracle datatypes listed in Table 6-3 in DataWindow objects and embedded SQL:

**Table 6-3: 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, PowerBuilder 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, PowerBuilder sends a Unicode string. The driver must guarantee that the data is saved as Unicode data correctly. When PowerBuilder retrieves character data, it assumes the data is Unicode.

Using the O84 database interface, PowerBuilder 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, PowerBuilder 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), PowerBuilder 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.

If you want to access Unicode data using NCHAR and NVARCHAR2 columns or stored procedure parameters, use PowerBuilder variables to store the Unicode data in a script using embedded SQL to avoid using a constant string, and force PowerBuilder to bind the variables.

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.

For example, suppose table1 has a column c1 with the datatype NVARCHAR2. To insert Unicode data into the table, set DisableBind to 0, set NCharBind to 1, and use this syntax:

string var1
insert into table1 (c1) values(:var1);

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 PowerBuilder 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 PowerBuilder is 0-6.