Oracle

DataDirect database drivers support the following Oracle data types:

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 NCHAR1When JDBCBehavior=0, the 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 higher, the second value applies.
NCLOB	CLOB or NCLOBOracle
NUMBER	DECIMAL
NUMBER(p, s)	DECIMAL
NVARCHAR2	VARCHAR or NVARCHAROracle
RAW	VARBINARY
TIMESTAMP2Supported only for Oracle 9i or higher.	TIMESTAMP
TIMESTAMP WITH LOCAL TIMEZONEOracle	TIMESTAMP
TIMESTAMP WITH TIMEZONE3When 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.Oracle	VARCHAR or TIMESTAMP
VARCHAR2Oracle	VARCHAR
XMLTypeOracle	CLOB or SQLXMOracle

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

Note the following conditions regarding 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 Designer-time Connection tab of an instance. For example, jdbc:tibcosoftwareinc:oracle://server_name:1521;databaseName=database_name;CatalogOptions=8 • TIBCO ActiveMatrix Adapter for Database 7.2 does not support complex data types of Oracle Database 12c multitenant container database (CDB) and pluggable databases (PDBs). However, TIBCO ActiveMatrix Adapter for Database 7.2 supports complex data types of Oracle Database 12c NON CDB. • 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. • When using an Oracle 11gR2 database, if you select a procedure containing the nested table type in the Call Operation tab and apply the setting, the following error message is displayed: AEADB-800032: Failed to create schema class for this operation. In addition, the selected procedure cannot be saved in your project.

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;