This article demonstrates how to use an Oracle sequence while inserting data.
This procedure was written with:
Talend verified this procedure to be compatible with:
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.
Create an Oracle table called PERSON with two fields: ID and Name. The table definition follows:
CREATE TABLE PERSON( ID Number(4) NOT NULL PRIMARY KEY, NAME VARCHAR(25), AGE Number(5), COUNTRY VARCHAR(20));
Create an Oracle sequence called sequence_demo:
CREATE SEQUENCE sequence_demo INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE
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:
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.
After you execute the Job, you will see that the data below was inserted into the table.