This scenario describes how to truncate the content of an Oracle database and load the content of an input file. The related Job is composed of three components that respectively creates the content, output this content into a file to be loaded into the Oracle database after the database table has been truncated.
Drop the following components: tOracleInput, tFileOutputDelimited and tOracleBulkExec from the Palette to the design workspace.
Connect the tOracleInput to the tFileOutputDelimited using a Row > Main link.
Connect the tOracleInput to the tOracleBulkExec using a OnSubjobOk trigger link.
Double-click the tOracleInput to open its Basic settings view.
Define the Oracle connection details. It is recommended to store the database connection details in the Metadata folder of the Repository tree view in order to retrieve them easily at any time in any Job.
Define the schema, if it is not stored in the Repository. In this example, the schema contains four columns as follows: ID_Contract, ID_Client, Contract_type and Contract_Value.
Define the tFileOutputDelimited component parameters, including output File Name, Row separator and Fields delimiter.
Double-click on the tOracleBulkExec to define the database populating properties.
In the Property Type list, select Repository if you stored the database connection details under the Metadata node of the Repository or select Built-in to define them manually. In this scenario, use the Built-in mode.
Set the connection parameters in the following fields: Host, Port, Database, Schema, Username, and Password.
Fill in the name of the Table to be populated and the Action on data to be carried out. In this use case, select insert.
In the Schema list, select Built-in, and click the [...] button next to the Edit schema field to define the structure of the data to be passed to the next component.
Click the Advanced settings view to configure the advanced settings of the component.
Select the Use an existing control file check box if you want to use a control file (.ctl) storing the status of the physical structure of the database. Or, fill in the following fields manually: Record format, Specify .ctl file's INTO TABLE clause manually, Field terminated by, Use field enclosure, Use schema's Date Pattern to load Date field, Specify field condition, Preserve blanks, Trailing null columns, Load options, NLS Language and Set Parameter NLS_TERRITORY according to your database.
In the Encoding list, select the encoding, or enter the encoding between double quotes if it does not exist in the list.
In the Output list, select to console to output the standard output of the database in the console.
Press Ctrl+S to save your Job.
Press F6 to run the Job. The log is shown in the console of the Run view and the table is populated with the parameter file data.
Related topic: see Scenario: Inserting data in MySQL database.