Oracle connector#

The Oracle connector allows querying and creating tables in an external Oracle database. Connectors let Trino join data provided by different databases, like Oracle and Hive, or different Oracle database instances.

Requirements#

To connect to Oracle, you need:

Oracle 12 or higher.
Network access from the Trino coordinator and workers to Oracle. Port 1521 is the default port.

Configuration#

To configure the Oracle connector as the oracle catalog, create a file named oracle.properties in etc/catalog. Include the following connection properties in the file:

connector.name=oracle
// The exact format of connection-url varies by Oracle version. Refer to
// the Oracle Database documentation for version-specific information on the
// JDBC Thin driver.
connection-url=jdbc:oracle:thin:@//example.net:1521/ORCLCDB
connection-user=root
connection-password=secret

The connection-url defines the connection information and parameters to pass to the Oracle JDBC Thin driver. See the Oracle Database JDBC driver documentation for more information.

The connection-user and connection-password are typically required and determine the user credentials for the connection, often a service user. You can use secrets to avoid actual values in the catalog properties files.

Note

Oracle does not expose metadata comment via REMARKS column by default in JDBC driver. You can enable it using oracle.remarks-reporting.enabled config option. See Additional Oracle Performance Extensions for more details.

By default, the Oracle connector uses connection pooling for performance improvement. The below configuration shows the typical default values. To update them, change the properties in the catalog configuration file:

oracle.connection-pool.max-size=30
oracle.connection-pool.min-size=1
oracle.connection-pool.inactive-timeout=20m

To disable connection pooling, update properties to include the following:

oracle.connection-pool.enabled=false

Multiple Oracle servers#

If you want to connect to multiple Oracle servers, configure another instance of the Oracle connector as a separate catalog.

To add another Oracle catalog, create a new properties file. For example, if you name the property file sales.properties, Trino creates a catalog named sales.

General configuration properties#

The following table describes general configuration properties for the connector:

Property name	Description	Default value
`case-insensitive-name-matching`	Match schema and table names case insensitively	False
`case-insensitive-name-matching.cache-ttl`		1 minute
`metadata.cache-ttl`	Duration for which metadata, including table and column statistics, is cached	0 (disabled caching)
`metadata.cache-missing`	Cache the fact that metadata, including table and column statistics, is not available	False
`metadata.cache-maximum-size`	Maximum number of objects stored in the metadata cache	10000
`write.batch-size`	Maximum number of statements in a batched execution. Do not change this setting from the default. Non-default values may negatively impact performance.	1000
`join-pushdown.enabled`	Enable join pushdown. Equivalent catalog session property is `join_pushdown_enabled`. Enabling this may negatively impact performance for some queries.	False

Non-transactional INSERT#

The connector supports adding rows using INSERT statements. By default, data insertion is performed by writing data to a temporary table. You can skip this step to improve performance and write directly to the target table. Set the insert.non-transactional-insert.enabled catalog property or the corresponding non_transactional_insert catalog session property to true.

Note that with this property enabled, data can be corrupted in rare cases where exceptions occur during the insert operation. With transactions disabled, no rollback can be performed.

Querying Oracle#

The Oracle connector provides a schema for every Oracle database.

Run SHOW SCHEMAS to see the available Oracle databases:

SHOW SCHEMAS FROM oracle;

If you used a different name for your catalog properties file, use that catalog name instead of oracle.

Note

The Oracle user must have access to the table in order to access it from Trino. The user configuration, in the connection properties file, determines your privileges in these schemas.

Examples#

If you have an Oracle database named web, run SHOW TABLES to see the tables it contains:

SHOW TABLES FROM oracle.web;

To see a list of the columns in the clicks table in the web database, run either of the following:

DESCRIBE oracle.web.clicks;
SHOW COLUMNS FROM oracle.web.clicks;

To access the clicks table in the web database, run the following:

SELECT * FROM oracle.web.clicks;

Type mapping#

Both Oracle and Trino have types that are not supported by the Oracle connector. The following sections explain their type mapping.

Oracle to Trino type mapping#

Trino supports selecting Oracle database types. This table shows the Oracle to Trino data type mapping:

Oracle to Trino type mapping#
Oracle database type	Trino type	Notes
`NUMBER(p, s)`	`DECIMAL(p, s)`	See Mapping numeric types
`NUMBER(p)`	`DECIMAL(p, 0)`	See Mapping numeric types
`FLOAT[(p)]`	`DOUBLE`
`BINARY_FLOAT`	`REAL`
`BINARY_DOUBLE`	`DOUBLE`
`VARCHAR2(n CHAR)`	`VARCHAR(n)`
`VARCHAR2(n BYTE)`	`VARCHAR(n)`
`NVARCHAR2(n)`	`VARCHAR(n)`
`CHAR(n)`	`CHAR(n)`
`NCHAR(n)`	`CHAR(n)`
`CLOB`	`VARCHAR`
`NCLOB`	`VARCHAR`
`RAW(n)`	`VARBINARY`
`BLOB`	`VARBINARY`
`DATE`	`TIMESTAMP`	See Mapping datetime types
`TIMESTAMP(p)`	`TIMESTAMP`	See Mapping datetime types
`TIMESTAMP(p) WITH TIME ZONE`	`TIMESTAMP WITH TIME ZONE`	See Mapping datetime types

Trino to Oracle type mapping#

Trino supports creating tables with the following types in an Oracle database. The table shows the mappings from Trino to Oracle data types:

Note

For types not listed in the table below, Trino can’t perform the CREATE TABLE <table> AS SELECT operations. When data is inserted into existing tables Oracle to Trino type mapping is used.

Trino to Oracle Type Mapping#
Trino type	Oracle database type	Notes
`TINYINT`	`NUMBER(3)`
`SMALLINT`	`NUMBER(5)`
`INTEGER`	`NUMBER(10)`
`BIGINT`	`NUMBER(19)`
`DECIMAL(p, s)`	`NUMBER(p, s)`
`REAL`	`BINARY_FLOAT`
`DOUBLE`	`BINARY_DOUBLE`
`VARCHAR`	`NCLOB`
`VARCHAR(n)`	`VARCHAR2(n CHAR)` or `NCLOB`	See Mapping character types
`CHAR(n)`	`CHAR(n CHAR)` or `NCLOB`	See Mapping character types
`VARBINARY`	`BLOB`
`DATE`	`DATE`	See Mapping datetime types
`TIMESTAMP`	`TIMESTAMP(3)`	See Mapping datetime types
`TIMESTAMP WITH TIME ZONE`	`TIMESTAMP(3) WITH TIME ZONE`	See Mapping datetime types

Mapping numeric types#

An Oracle NUMBER(p, s) maps to Trino’s DECIMAL(p, s) except in these conditions:

No precision is specified for the column (example: NUMBER or NUMBER(*)), unless oracle.number.default-scale is set.
Scale (s ) is greater than precision.
Precision (p ) is greater than 38.
Scale is negative and the difference between p and s is greater than 38, unless oracle.number.rounding-mode is set to a different value than UNNECESSARY.

If s is negative, NUMBER(p, s) maps to DECIMAL(p + s, 0).

For Oracle NUMBER (without precision and scale), you can change oracle.number.default-scale=s and map the column to DECIMAL(38, s).

Mapping datetime types#

Selecting a timestamp with fractional second precision (p) greater than 3 truncates the fractional seconds to three digits instead of rounding it.

Oracle DATE type may store hours, minutes, and seconds, so it is mapped to Trino TIMESTAMP.

Warning

Due to date and time differences in the libraries used by Trino and the Oracle JDBC driver, attempting to insert or select a datetime value earlier than 1582-10-15 results in an incorrect date inserted.

Mapping character types#

Trino’s VARCHAR(n) maps to VARCHAR2(n CHAR) if n is no greater than 4000. A larger or unbounded VARCHAR maps to NCLOB.

Trino’s CHAR(n) maps to CHAR(n CHAR) if n is no greater than 2000. A larger CHAR maps to NCLOB.

Using CREATE TABLE AS to create an NCLOB column from a CHAR value removes the trailing spaces from the initial values for the column. Inserting CHAR values into existing NCLOB columns keeps the trailing spaces. For example:

CREATE TABLE vals AS SELECT CAST('A' as CHAR(2001)) col;
INSERT INTO vals (col) VALUES (CAST('BB' as CHAR(2001)));
SELECT LENGTH(col) FROM vals;

 _col0
-------
  2001
     1
(2 rows)

Attempting to write a CHAR that doesn’t fit in the column’s actual size fails. This is also true for the equivalent VARCHAR types.

General configuration properties#

The following properties can be used to configure how data types from the connected data source are mapped to Trino data types and how the metadata is cached in Trino.

Property name

Description

Default value

unsupported-type-handling

Configure how unsupported column data types are handled:

IGNORE, column is not accessible.
CONVERT_TO_VARCHAR, column is converted to unbounded VARCHAR.

The respective catalog session property is unsupported_type_handling.

IGNORE

jdbc-types-mapped-to-varchar

Allow forced mapping of comma separated lists of data types to convert to unbounded VARCHAR

Number to decimal configuration properties#

Configuration property name

Session property name

Description

Default

oracle.number.default-scale

number_default_scale

Default Trino DECIMAL scale for Oracle NUMBER (without precision and scale) date type. When not set then such column is treated as not supported.

not set

oracle.number.rounding-mode

number_rounding_mode

Rounding mode for the Oracle NUMBER data type. This is useful when Oracle NUMBER data type specifies higher scale than is supported in Trino. Possible values are:

UNNECESSARY - Rounding mode to assert that the requested operation has an exact result, hence no rounding is necessary.
CEILING - Rounding mode to round towards positive infinity.
FLOOR - Rounding mode to round towards negative infinity.
HALF_DOWN - Rounding mode to round towards nearest neighbor unless both neighbors are equidistant, in which case rounding down is used.
HALF_EVEN - Rounding mode to round towards the nearest neighbor unless both neighbors are equidistant, in which case rounding towards the even neighbor is performed.
HALF_UP - Rounding mode to round towards nearest neighbor unless both neighbors are equidistant, in which case rounding up is used
UP - Rounding mode to round towards zero.
DOWN - Rounding mode to round towards zero.

UNNECESSARY

Synonyms#

Based on performance reasons, Trino disables support for Oracle SYNONYM. To include SYNONYM, add the following configuration property:

oracle.synonyms.enabled=true

Pushdown#

The connector supports pushdown for a number of operations:

SQL support#

The connector provides read access and write access to data and metadata in Oracle. In addition to the globally available and read operation statements, the connector supports the following statements:

SQL DELETE#

If a WHERE clause is specified, the DELETE operation only works if the predicate in the clause can be fully pushed down to the data source.

ALTER TABLE#

The connector does not support renaming tables across multiple schemas. For example, the following statement is supported:

ALTER TABLE catalog.schema_one.table_one RENAME TO catalog.schema_one.table_two

The following statement attempts to rename a table across schemas, and therefore is not supported:

ALTER TABLE catalog.schema_one.table_one RENAME TO catalog.schema_two.table_two