Oracle Bulk Loader


This step type 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.




Step name

Name of the step.

Note: This name has to be unique in a single transformation.


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

Target schema

The name of the Schema for the table to write data to. This is important for data sources that allow for table names with dots '.' in it.

Target table

Name of the target table.

Sqldr path

Full path to the sqlldr utility (including sqlldr). If sqlldr is in the path of the executing application you can leave it to sqlldr.

Load method

Either "Automatic load (at the end)", "Manual load (only creation of files)", or "Automatic load (on the fly)". Automatic load (at the end) will start up sqlldr after receiving all input with the specified arguments in this step. Manual load will only create a control and data file, this can be used as a back-door: you can have PDI generate the data and create e.g. your own control file to load the data (outside of this step).  Automatic load (on the fly) will start up sqlldr and pipe data to sqlldr as input is received by this step.

Note: "Automatic load (on the fly)" requires your operating system to support passing data='-' to sqlldr to load data from stdin rather than an actual file.

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.


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, optionally defined.

Bad file

The name of the bad file, optionally defined.

Discard file

The name of the discard file, optionally defined.


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

Direct path

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

Erase cfg/dat files after use

When switched on the control and data file will be erased after loading.

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 Oracle table;
  • Stream field: Field to be taken from the incoming rows;
  • Date mask: Either "Date" or "Date mask", determines how date/timestamps will be loaded in Oracle. When left empty defaults to "Date" in case of dates.  The "Date" type truncates values to the day-of-month, whereas the DateTime option passes date and time information.

Metadata Injection Support (7.x and later)

All fields of this step support metadata injection. You can use this step with ETL Metadata Injection to pass metadata to your transformation at runtime.