Supported Oracle datatypes – PB Docs 2022

The Oracle database interfaces support the Oracle datatypes listed
in the following table in DataWindow objects and embedded SQL.

The ORA driver adds support for the XMLType datatype that was
introduced with Oracle 9i. However, you cannot use this datatype with
embedded SQL statements or in a DataWindow object.

Accessing Unicode data

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.

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 Oracle 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
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:

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 Oracle 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:

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.