Procedure

Often you want fields, especially the key field, to be increased while inserting, using the sequence defined in Oracle. In Talend Studio, the sequence is used in the Additional columns table, in the Advanced settings tab of the tOracleOutput component.

Additional Columns are designed to handle DB columns which need DB SQL instructions, but where there is no data from the data flow of the Job.

The following examples illustrate how to use an Oracle sequence while inserting.

Table and sequence definition

Create an Oracle table called PERSON with two fields: ID and Name. The table definition follows:

Create an example Job

Create a demo Job named UsingOracleSeqDemo. The Job will read data from a text file and insert the data into Oracle's PERSON table. On the tOracleOutput component, it uses the Oracle sequence in the Additional columns table to generate an increasing number for the ID column.

The source text file contains the following content:

in.txt

Shong;30;CN
Ross;34;US
Patrick;27;FR
Pedro;CN

The detailed Job settings can be seen below; the job is also available as an attachment.

Execute the Job

After you execute the Job, you will see that the data below was inserted into the table.

Sorry for the lack of reply - if this is still an open question, please consider posting it to one of the Community discussion boards where more people might have suggestions. Or for technical help, please contact our Support team at https://www.talend.com/services/technical-support/ .