Oracle Output

Dark
Light

Article Summary

Share feedback

Thanks for sharing your feedback!

This article is specific to the following platforms - Snowflake - Redshift.

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.

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

Snowflake Properties
Property	Setting	Description
Name	String	A human-readable name for the component.
Driver	.jar File	Use the Manage Database Drivers dialog to create, browse, and test JDBC drivers. Please 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 password of the Oracle account. Users can store their password within the component, or securely situate their password within the Matillion ETL Password Manager.
JDBC Options	Parameter	A JDBC connection parameter supported by the database driver.
JDBC Options	Value	The value of the corresponding parameter.
Database	Select	Select the Snowflake database. The special value, [Environment Default], will use the database defined in the Matillion ETL environment. For more information, read Databases, Tables, & Views.
Schema	Select	Select the Snowflake schema. The special value, [Environment Default], will use the schema defined in the Matillion ETL environment. For more information, read Database, Schema, & Share DDL.
Source Table	Select	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	Column Selector	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	Select	Define how the target table is treated. Create If Not Exists: if the named target table does not 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. Please use this setting with care.
Primary Key	Column Select	A column or group of columns used to identify a row uniquely in a table.
Update Strategy	Select	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.
Update Strategy	Select	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	Select	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	Select	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.

Redshift Properties
Property	Setting	Description
Name	String	A human-readable name for the component.
Driver	.jar File	Use the Manage Database Drivers dialog to create, browse, and test JDBC drivers. Please 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 password of the Oracle account. Users can store their password within the component, or securely situate their password within the Matillion ETL Password Manager.
JDBC Options	Parameter	A JDBC connection parameter supported by the database driver.
JDBC Options	Value	The value of the corresponding parameter.
Source Schema	Select	Select the source schema. The special value, [Environment Default], will use the schema defined in the Matillion ETL environment.
Source Table	Select	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	Column Select	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	Select	Define how the target table is treated. Create If Not Exists: if the named target table does not 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. Please use this setting with care.
Primary Key	Column Select	A column or group of columns used to identify a row uniquely in a table.
Update Strategy	Select	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.
Update Strategy	Select	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	Select	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	Select	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.