Oracle Translator (oracle)

Also see common JDBC Translator Information

The Oracle Translator, known by the type name oracle, is for use with Oracle 9i or later.

Note	The Oracle provide JDBC driver may cause memory issues due to excessive buffer usage. Please see a related issue and an Oracle whitepaper.

Importer Properties

useGeometryType- Use the Teiid Geometry type when importing columns with a source type of SDO_GEOMETRY. Defaults to false.

Note	Metadata import from Oracle may be slow. It is recommended that at least a schema name filter is specified. There is also the useFetchSizeWithLongColumn=true connection property that can increase the fetch size for metadata queries.

it significantly improves the metadata load process, especially when there are a large number of tables in a schema. See doc [1]

Teiid should either somehow enforce or document this.

Execution Properties

OracleSuppliedDriver- indicates that the Oracle supplied driver (typically prefixed by ojdbc) is being used. Defaults to true. Set to false when using DataDirect or other Oracle JDBC drivers.

Oracle Specific Metadata

Sequences

Sequences may be used with the Oracle translator. A sequence may be modeled as a table with a name in source of DUAL and columns with the name in source set to <sequence name>.[nextval|currval]

With Teiid 10.0+ sequences may be imported automatically import properties.

Teiid 8.4 and Prior Oracle Sequence DDL

CREATE FOREIGN TABLE seq (nextval integer OPTIONS (NAMEINSOURCE 'seq.nextval'), currval integer options (NAMEINSOURCE 'seq.currval') ) OPTIONS (NAMEINSOURCE 'DUAL')

With Teiid 8.5 it’s no longer necessary to rely on a table representation and Oracle specific handling for sequences. See DDL Metadata for representing currval and nextval as source functions.

8.5 Example:Sequence Native Query

CREATE FOREIGN FUNCTION seq_nextval () returns integer OPTIONS ("teiid_rel:native-query" 'seq.nextval');

You can also use a sequence as the default value for insert columns by setting the column to autoincrement and the name in source to <element name>:SEQUENCE=<sequence name>.<sequence value>.

Rownum

A rownum column can also added to any Oracle physical table to support the rownum pseudo-column. A rownum column should have a name in source of rownum. These rownum columns do not have the same semantics as the Oracle rownum construct so care must be taken in their usage.

Out Parameter Result Set

Out parameters for procedures may also be used to return a result set, if this is not represented correctly by the automatic import you need to manually create a result set and represent the output parameter with native type "REF CURSOR".

DDL for out parameter result set

create foreign procedure proc (in x integer, out y object options (native_type 'REF CURSOR'))
returns table (a integer, b string)

Geo Spatial function support

Oracle translator supports geo spatial functions. The supported functions are:

Relate = sdo_relate

CREATE FOREIGN FUNCTION sdo_relate (arg1 string,  arg2 string,  arg3 string) RETURNS string;
CREATE FOREIGN FUNCTION sdo_relate (arg1 Object,  arg2 Object,  arg3 string) RETURNS string;
CREATE FOREIGN FUNCTION sdo_relate (arg1 string,  arg2 Object,  arg3 string) RETURNS string;
CREATE FOREIGN FUNCTION sdo_relate (arg1 Object,  arg2 string,  arg3 string) RETURNS string;

Nearest_Neighbor = sdo_nn

CREATE FOREIGN FUNCTION sdo_nn (arg1 string,  arg2 Object,  arg3 string,  arg4 integer) RETURNS string;
CREATE FOREIGN FUNCTION sdo_nn (arg1 Object,  arg2 Object,  arg3 string,  arg4 integer) RETURNS string;
CREATE FOREIGN FUNCTION sdo_nn (arg1 Object,  arg2 string,  arg3 string,  arg4 integer) RETURNS string;

Within_Distance = sdo_within_distance

CREATE FOREIGN FUNCTION sdo_within_distance (arg1 Object,  arg2 Object,  arg3 string) RETURNS string;
CREATE FOREIGN FUNCTION sdo_within_distance (arg1 string,  arg2 Object,  arg3 string) RETURNS string;
CREATE FOREIGN FUNCTION sdo_within_distance (arg1 Object,  arg2 string,  arg3 string) RETURNS string;

Nearest_Neigher_Distance = sdo_nn_distance

CREATE FOREIGN FUNCTION sdo_nn_distance (arg integer) RETURNS integer;

Filter = sdo_filter

CREATE FOREIGN FUNCTION sdo_filter (arg1 Object,  arg2 string,  arg3 string) RETURNS string;
CREATE FOREIGN FUNCTION sdo_filter (arg1 Object,  arg2 Object,  arg3 string) RETURNS string;
CREATE FOREIGN FUNCTION sdo_filter (arg1 string,  arg2 object,  arg3 string) RETURNS string;

Pushdown Functions

The Oracle translator, depending upon the version of Oracle, will register other non-geospatial pushdown functions with the engine. This includes:

TRUNC, both numeric and timestamp versions
LISTAGG, which requires the Teiid SQL syntax "LISTAGG(arg [, delim] ORDER BY …)"

SQLXML

If you need to retrieve SQLXML values from oracle and are getting oracle.xdb.XMLType or OPAQUE instances instead, you need to use client driver later than 11.2, have the xdb.jar and xmlparserv2.jar jars in the classpath, and set the system property oracle.jdbc.getObjectReturnsXMLType="false". See also the Oracle documentation.

Oracle Translator