Skip to content

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

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.

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.

Source Schema = drop-down

Select the table schema. The special value, [Environment Default], will use the schema defined in the environment. For more information on using multiple schemas, read Schemas.


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.


Video


Snowflake Delta Lake on Databricks Amazon Redshift Google BigQuery Azure Synapse Analytics