Oracle

The following Oracle data types are supported by DataDirect database drivers:

Oracle Data Type JDBC Data Type
BLOB BLOB
CHAR CHAR
CLOB CLOB
DATE TIMESTAMP
FLOAT(n) DOUBLE
LONG LONGVARCHAR
LONG RAW LONGVARBINARY
NCHAR CHAR or NCHAR
Note: When JDBCBehavior=0, this data type depends on the JVM used by the application. For JVMs earlier than Java SE 6, the first value applies. For Java SE 6 and later versions, the second value applies.
NCLOB CLOB or NCLOB
Note: When JDBCBehavior=0, this data type depends on the JVM used by the application. For JVMs earlier than Java SE 6, the first value applies. For Java SE 6 and later versions, the second value applies.
NUMBER DECIMAL
NUMBER(p, s) DECIMAL
NVARCHAR2 VARCHAR or NVARCHAR
Note: When JDBCBehavior=0, this data type depends on the JVM used by the application. For JVMs earlier than Java SE 6, the first value applies. For Java SE 6 and later versions, the second value applies.
RAW VARBINARY
TIMESTAMP
Note: Supported only for Oracle 9i and later versions.
TIMESTAMP
TIMESTAMP WITH LOCAL TIMEZONE
Note: Supported only for Oracle 9i and later versions.
TIMESTAMP
TIMESTAMP WITH TIMEZONE
Note: Supported only for Oracle 9i and later versions.
Note: When FetchTSWTZasTimestamp=false (default), this data type is mapped to the JDBC VARCHAR data type; when FetchTSWTZasTimestamp=true, it is mapped to the JDBC TIMESTAMP data type.
VARCHAR or TIMESTAMP
VARCHAR2
Note: Supported only for Oracle 9i and later versions.
VARCHAR
XMLType CLOB or SQLXM
Note: When JDBCBehavior=0, this data type depends on the JVM used by the application. For JVMs earlier than Java SE 6, the first value applies. For Java SE 6 and later versions, the second value applies.

Complex Data Types

Request-Response Service in the custom RPC mode supports Oracle complex data types, which are defined by the standard data types. Currently the following three Oracle complex data types are supported:

Object Type
You can use this data type in the same ways that you use standard data types such as NUMBER or VARCHAR2. For example, you can specify an object type as the data type of a column in a relational table, and you can declare variables of an object type. The value is a variable or an instance of that type. An object instance is also called an object.
Nested Table Type
This is one of the collection data types. A nested table can have any number of elements and is not in order. If you want to run efficient queries on a collection, handle arbitrary numbers of elements, or perform mass insert, update, or delete operations, then use a nested table.
Varray Type
This is one of the collection data types. A varray is an ordered collection of elements. If you want to store only a fixed number of items, or loop through the elements in order, or if you want to retrieve and manipulate the entire collection as a value, then use a varray.

Note the following conditions about Oracle complex data types:

  • Before using Oracle complex data types, you have to add the CatalogOptions= 8 property to the JDBC URL field in the Adapter for Database Connection Configuration window. For example,

    jdbc:tibcosoftwareinc:oracle://server_name:1521;databaseName=database_name;CatalogOptions=8

  • The complex data types of Oracle Database 12c multitenant container database (CDB) and pluggable databases (PDBs) are not supported. However, the complex data types of Oracle Database 12c NON CDB are supported.
  • Request-Response Service in the custom RPC mode supports the invocation of stored procedures that contain the three Oracle complex data types.
  • Oracle functions, procedures, and packages all support these complex data types.

Nested Complex Data Types

Complex data types can also nest complex data types. The following examples show nested complex data types:

Example 1 
create TYPE num_varray AS VARRAY(4) OF NUMBER(12,2);
create TYPE num_object AS OBJECT(
a num_varray,
b number(8,0)
);
Example 2 
create TYPE float_varray AS VARRAY(4) OF float;
create TYPE float_table AS TABLE OF float_varray;
Example 3:
create TYPE char_object as OBJECT(
a char,
b varchar2(5)
);
CREATE TYPE char_table AS TABLE OF char_object;