Using an Oracle sequence while inserting data

 Overview

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

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 folows:

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: 

  
  

This Job is also available in the attachments list.

 Execute the Job

Execute the Job. The data as seen below is inserted into the table.

Related Files

  File Modified
  ZIP Archive usingoraclesequencedemo.zip Nov 19, 2012 byShicong Hong
Version History
Revision #:
1 of 1
Last update:
‎04-17-2017 10:36 PM
Updated by:
 
Labels (1)
Contributors