Using an Oracle sequence while inserting data

Overview

This article demonstrates how to use an Oracle sequence while inserting data.

 

Environment

This procedure was written with:

  • Talend Open Studio for DI 5.0-r72978
  • JDK version: Sun JDK build 1.6.0_26-b03
  • Operating system: Windows XP SP3
  • Oracle Database 10g Enterprise Edition Release 10.1.0.2.

Talend verified this procedure to be compatible with:

  • Data Integration releases: 4.2.3, 4.2.4, 5.0.0, 5.0.1, 5.0.2, 5.1.1, 5.1.2

 

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 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 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.

1_050.png

 

 2_044.png

 

3_025.png

 

Execute the Job

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

4_022.png 

 

Version history
Revision #:
2 of 2
Last update:
‎06-21-2017 04:30 PM
Updated by:
 
Labels (1)