Oracle Output
Overview
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 = string
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 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. The available parameters are explained in the data model. 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. Please 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 = string
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 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. The available parameters are explained in the data model. 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. Please 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 |
|---|---|---|---|---|
| ✅ | ❌ | ✅ | ❌ | ❌ |