Oracle Output

The Oracle Output component enables users to output the contents of a table (or view) from their cloud data warehouse (CDW) to their Oracle database.

Using this component, Matillion ETL users can push their data to an on-prem server from the cloud if they so wish.

Note

Snowflake Data Type Mapping

Oracle does not support a Boolean data type. In light of this, Boolean values will be converted to a binary "1" or "0". Any Snowflake Booleans with a value of "true" will have a binary value of "1" after the Oracle Output job. Any Snowflake Booleans with a value of "false" will have a binary value of "0" after the Oracle Output job.

Properties

SnowflakeAmazon Redshift

Name = string

A human-readable name for the component.

Driver = database driver modal

Use the Manage Database Drivers modal to create, browse, and test JDBC drivers. Upload a .jar file when creating a new database driver. You may upload more than one file if required, for example, if the driver you are uploading is split into multiple packages.

Endpoint = string

The Oracle database endpoint. Typically, this is your Oracle database IP. For example, 10.12.2.15.

Port = integer

The port number that follows your Oracle database endpoint. The default value is 1521.

Database Name = string

The name of your Oracle database.

Username = string

The username of the Oracle account.

Password = string

The corresponding password. Store the password in the component, or create a managed entry for the password using Manage Passwords (recommended).

JDBC Options = column editor

Parameter: A JDBC parameter supported by the database driver. Manual setup is not usually required, since sensible defaults are assumed.
Value: A value for the given Parameter.

Database = drop-down

The Snowflake database. The special value, [Environment Default], will use the database defined in the environment. Read Databases, Tables and Views - Overview to learn more.

Schema = drop-down

The Snowflake schema. The special value, [Environment Default], will use the schema defined in the environment. Read Database, Schema, and Share DDL to learn more.

Source Table = drop-down

Select the source table to be output to the Oracle database. The chosen source schema determines what tables will be available for selection.

Target Table = string

A name for the output table that is to be created in the Oracle database.

Target Schema = string

The name of the Oracle schema to output the target table into.

Load Columns = dual listbox

The columns from the source table to include in the output job. Use the arrow buttons to include or exclude columns. Columns on the right-hand side will be included. Columns on the left-hand side will be excluded.

Table Maintenance drop-down

Define how the target table is treated.

Create If Not Exists: If the named target table doesn't yet exist, it will be created.
None: Assume the Oracle database already has the table defined with the correct structure.
Replace: if the named target table already exists, it will be dropped and replaced by a newly created table. Use this setting with care.

Primary Key = dual listbox

Select one or more columns to be designated as the table's primary key.

Update Strategy = drop-down

In addition to inserting new records based on the primary key(s), this property can instruct Matillion ETL to:

Ignore: Existing rows with the same primary key values will be ignored.
Replace: Existing rows with the same primary key values will be replaced.

The default setting is Ignore. This property is only available after a primary key has been selected.

Truncate Target Table = drop-down

Yes removes all rows from a table or specified partitions of a table, without logging the individual row deletions. For more information, read TRUNCATE TABLE.
No: does not truncate the target table.

On Warnings = drop-down

Specify whether an output load should Continue or Fail if an ANSI Warning message is generated.

Batch Size = integer

The number of rows to load to the target between each COMMIT. The default value is 5000.