Hitachi Vantara Pentaho Community Wiki
Access Keys:
Skip to content (Access Key - 0)


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.


Option Description
Step name Name of the step.

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

Connection 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.
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, optionally defined.
Bad file The name of the bad file, optionally defined.
Discard file The name of the discard file, optionally defined.
Encoding 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.

This documentation is maintained by the Pentaho community, and members are encouraged to create new pages in the appropriate spaces, or edit existing pages that need to be corrected or updated.

Please do not leave comments on Wiki pages asking for help. They will be deleted. Use the forums instead.

Adaptavist Theme Builder (4.2.0) Powered by Atlassian Confluence 3.3.3, the Enterprise Wiki