Contents x
Oracle Output
  • Dark
    Light
Contents

Oracle Output

  • Dark
    Light
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.
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.
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.

Video

What's Next

Cookie consent

We use our own and third-party cookies to understand how you interact with our knowledge base. This helps us show you more relevant content based on your browsing and navigation history.