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
Create an Oracle table called PERSON with four fields:
AGE and COUNTRY, as
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
- You have created a text file, called person_table.txt,
containing the following
Shong;30;CN Ross;34;US Patrick;27;FR Pedro;28;CN
- Drop the following components from the Palette to the design workspace: tFileInputDelimited and tOracleOutput.
Link the two components using a
Set the input component
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
- You have created the Oracle table and sequence.
- 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
- Save your Job.
Press F6 to execute it.
The data containing in the person_table.txt file is inserted into the TABLE_PERSON table.