Striim 3.10.3 documentation

Table of Contents

Oracle Reader data type support and correspondence

Oracle type

TQL type

ADT

not supported

ARRAY

not supported

BFILE

BFILE values will be set to null

BINARY_DOUBLE

double

BINARY_FLOAT

float

BLOB

string (a primary or unique key must exist on the table)

An insert or update containing a column of this type generates two CDC log entries: an insert or update in which the value for this column is null, followed by an update including the value.

CHAR

string

CLOB

string (a primary or unique key must exist on the table)

An insert or update containing a column of this type generates two CDC log entries: an insert or update in which the value for this column is null, followed by an update including the value.

DATE

DateTime

FLOAT

string

INTERVALDAYTOSECOND

string (when using LogMiner, string always has a sign; when using XStream, string begins with minus sign if value is negative, but the plus sign is omitted for positive values)

INTERVALYEARTOMONTH

string (when using LogMiner, string always has a sign; when using XStream, string begins with minus sign if value is negative, but the plus sign is omitted for positive values)

LONG

Results may be inconsistent. Oracle recommends using CLOB instead.

LONG RAW

Results may be inconsistent. Oracle recommends using CLOB instead.

NCHAR

string

NCLOB

string (a primary or unique key must exist on the table)

NESTED TABLE

not supported

NUMBER

string

NVARCHAR2

string

RAW

string

REF

not supported

ROWID

not supported

SD0_GEOMETRY

SD0_GEOMETRY values will be set to null

TIMESTAMP

DateTime

TIMESTAMP WITH LOCAL TIME ZONE

DateTime (supported in LogMiner only)

TIMESTAMP WITH TIME ZONE

DateTime (supported in LogMiner only)

UDT

not supported

UROWID

not supported

VARCHAR2

string

VARRAY

Supported by LogMiner only in Oracle 12c and later. Required Oracle Reader settings:

  • Committed Transactions: True

  • Dictionary Mode: OnlineCatalog

  • Undo Retention: Set to an interval long enough that VARRAY values will be available when Oracle Reader attempts to read them. If the interval is too short and the data is no longer in the log, Oracle Reader will crash with java.sql.SQLException "ORA-30052: invalid lower limit snapshot expression."

Limitations:

  • Tables containing VARRAY values must have primary keys.

  • The VARRAY must contain only elements that can be returned as Java primitive types.

  • The VARRAY's type name must be unique to its schema. If the same VARRAY type name is used in another schema, Oracle Reader will crash with java.sql.SQLException "ORA-01427: single-row subquery returns more than one row."

  • Oracle's UNDO_RETENTION policy must be set to an interval long enough that VARRAY values will be available when Oracle Reader attempts to retrieve them with a Flashback (SELECT AS OF) query. If the interval is too short and the data is no longer available, Oracle Reader will crash with java.sql.SQLException "ORA-30052: invalid lower limit snapshot expression." For more information, see the documentation for UNDO_RETENTION for your version of Oracle.

When the output of an Oracle Reader source is the input of a target using XML Formatter, the formatter's Format Column Value As property must be set to xmlelement for VARRAY data to be formatted correctly.

XMLTYPE

Supported only for Oracle 12c and later. When DictionaryMode is OnlineCatalog, values in any XMLType columns will be set to null. When DictionaryMode is OfflineCatalog, reading from tables containing XMLType columns is not supported.