Using an Oracle sequence while inserting data

You can genarate automatically primary key values while inserting data, 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.

The following example illustrates how to use an Oracle sequence while inserting data.

Create the Oracle table and sequence

Procedure

Create an Oracle table called PERSON with four fields: ID, NAME, AGE and COUNTRY, as follows.

CREATE TABLE TABLE_PERSON 
(
  ID NUMBER(4) NOT NULL 
, NAME VARCHAR2(25) 
, AGE NUMBER(5) 
, COUNTRY VARCHAR2(20) 
, CONSTRAINT PERSON_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
);

Create an Oracle sequence called sequence_demo.

CREATE SEQUENCE SEQUENCE_DEMO 
INCREMENT BY 1 
START WITH 1 
NOMAXVALUE
NOCYCLE
NOCACHE;

Read data from a text file and insert the data into Oracle's PERSON table

Before you begin

You have created a text file, called person_table.txt, containing the following information.
```
Shong;30;CN
Ross;34;US
Patrick;27;FR
Pedro;28;CN
```

Procedure

Drop the following components from the Palette to the design workspace: tFileInputDelimited and tOracleOutput.
Link the two components using a Row > Main connection.

Set the input component

Procedure

Double-click the tFileInputDelimited component to open its Basic settings view.
In the File Name field, browse to the input file, in this example person_table.txt.
Click [...] next to the Edit schema field to describe the structure of the input file.
Click [+] to add the three columns, name as String, age as Integer and country as String.

Set the output component

Before you begin

You have created the Oracle table and sequence.

Procedure

Double-click the tOracleOutput to open its Basic settings view.
Define the Oracle connection details.
In this example, the database connection details are stored in the Metadata folder of the Repository tree view.
In the Table field, enter the name of the table into which the data will be written, in this example TABLE_PERSON.
From the Action on table list, leave Default and from the Action on data list, select Insert to write the data.
Click Sync columns to retrieve the schema from the preceding component.
Click the Advanced settings tab to set the advanced parameters.
In the Additional columns area, click the [+] to add a column as follows.
- Type in "ID" in the Name field.
- In the SQL expression cell, type in the SQL statement to be executed, "sequence_demo.NEXTVAL" in this example.
- Select Before from the Position list and select name from the Reference column list.

Execute the Job

Procedure

Save your Job.
Press F6 to execute it.

The data containing in the person_table.txt file is inserted into the TABLE_PERSON table.