Oracle Bulk Loader

Description

This transform allows you to bulk load data to an Oracle database. It will write the data it receives to a proper load format and will then invoke Oracle SQL*Loader to transfer it to the specified table.

Supported Engines

Hop Engine

Supported

Spark

Maybe Supported

Flink

Maybe Supported

Dataflow

Maybe Supported

Options

Option	Description
Transform name	Name of the transform.
Connection	Name of the database connection on which the target table resides.
Target schema	The name of the Schema for the table to write data to.
Target table	Name of the target table.
Sqldr path	Full path to the sqlldr utility (including sqlldr).
Load method	The SQLLoader method to use when loading data: Automatic load (at the end) will start up sqlldr after receiving all input with the specified arguments in this transform. * Manual load will only create a control and data file, this can be used as a back-door: you can have Hop generate the data and create e.g. your own control file to load the data (outside of this transform). * Automatic load (on the fly) will start up sqlldr and pipe data to sqlldr as input is received by this transform.
Load action	Append, Insert, Replace, Truncate. These map to the sqlldr action to be performed.
Maximum errors	The number of rows in error after which sqlldr will abort. This corresponds to the "ERROR" attribute of sqlldr.
Commit	The number of rows after which to commit, this corresponds to the "ROWS" attribute of sqlldr which differs between using a conventional and a direct path load.
Bind Size	Corresponds to the "BINDSIZE" attribute of sqlldr.
Read Size	Corresponds to the "READSIZE" attribute of sqlldr.
Control file	The name of the file used as control file for sqlldr.
Data file	The name of the data file in which the data will be written.
Log file	The name of the log file (optional).
Bad file	The name of the bad file that will contain the rejected records (optional).
Discard file	The name of the discard file that will contain filtered records that did not match any record-selection criteria specified in the control file (optional).
Encoding	Encodes data in a specific encoding, any valid encoding can be chosen besides the one in the drop down list.
Oracle Character Set	The Oracle character set to use.
Alternate Record Terminator	Specify this value to override SQL*Loader''s default record terminator.
Direct path	Switch on direct path loading (corresponds to DIRECT=TRUE in sqlldr).
Erase cfg/dat files after use	This option causes the control and data files to be erased after use.
Fail on warning	This option causes the transform to fail on load warnings.
Fail on error	This option causes the transform to fail on load errors.
Load data in parallel	This option causes data to be loaded in parallel. Parallel loading is only possible with a direct path option.
Fields to load	This table contains a list of fields to load data from, properties include: * Table field: Table field to be loaded in the PostgreSQL table; * Stream field: Field to be taken from the incoming rows; * Date mask: Either "Pass through, "Date" or "DateTime", determines how date/timestamps will be loaded in Oracle.

Option

Description

Transform name

Name of the transform.

Connection

Name of the database connection on which the target table resides.

Target schema

The name of the Schema for the table to write data to.

Target table

Name of the target table.

Sqldr path

Full path to the sqlldr utility (including sqlldr).

Load method

The SQL*Loader method to use when loading data: * Automatic load (at the end) will start up sqlldr after receiving all input with the specified arguments in this transform. * Manual load will only create a control and data file, this can be used as a back-door: you can have Hop generate the data and create e.g. your own control file to load the data (outside of this transform). * Automatic load (on the fly) will start up sqlldr and pipe data to sqlldr as input is received by this transform.

Load action

Append, Insert, Replace, Truncate. These map to the sqlldr action to be performed.

Maximum errors

The number of rows in error after which sqlldr will abort. This corresponds to the "ERROR" attribute of sqlldr.

Commit

The number of rows after which to commit, this corresponds to the "ROWS" attribute of sqlldr which differs between using a conventional and a direct path load.

Bind Size

Corresponds to the "BINDSIZE" attribute of sqlldr.

Read Size

Corresponds to the "READSIZE" attribute of sqlldr.

Control file

The name of the file used as control file for sqlldr.

Data file

The name of the data file in which the data will be written.

Log file

The name of the log file (optional).

Bad file

The name of the bad file that will contain the rejected records (optional).

Discard file

The name of the discard file that will contain filtered records that did not match any record-selection criteria specified in the control file (optional).

Encoding

Encodes data in a specific encoding, any valid encoding can be chosen besides the one in the drop down list.

Oracle Character Set

The Oracle character set to use.

Alternate Record Terminator

Specify this value to override SQL*Loader''s default record terminator.

Direct path

Switch on direct path loading (corresponds to DIRECT=TRUE in sqlldr).

Erase cfg/dat files after use

This option causes the control and data files to be erased after use.

Fail on warning

This option causes the transform to fail on load warnings.

Fail on error

This option causes the transform to fail on load errors.

Load data in parallel

This option causes data to be loaded in parallel. Parallel loading is only possible with a direct path option.

Fields to load

This table contains a list of fields to load data from, properties include: * Table field: Table field to be loaded in the PostgreSQL table; * Stream field: Field to be taken from the incoming rows; * Date mask: Either "Pass through, "Date" or "DateTime", determines how date/timestamps will be loaded in Oracle.