Using an Oracle sequence while inserting data

author
Shicong Hong
EnrichVersion
6.4
6.3
6.2
6.1
EnrichProdName
Talend Open Studio for ESB
Talend Data Fabric
Talend ESB
Talend Open Studio for MDM
Talend Big Data Platform
Talend Big Data
Talend Open Studio for Data Integration
Talend Real-Time Big Data Platform
Talend Data Integration
Talend MDM Platform
Talend Open Studio for Big Data
Talend Data Services Platform
Talend Data Management Platform
task
Design and Development > Designing Jobs
Data Quality and Preparation > Third-party systems > Database components > Oracle components
Data Governance > Third-party systems > Database components > Oracle components
Design and Development > Third-party systems > Database components > Oracle components
EnrichPlatform
Talend Studio

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

  1. 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 
    );
  2. 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 information.
    Shong;30;CN
    Ross;34;US
    Patrick;27;FR
    Pedro;28;CN
  1. Drop the following components from the Palette to the design workspace: tFileInputDelimited and tOracleOutput.
  2. Link the two components using a Row > Main connection.

Set the input component

  1. Double-click the tFileInputDelimited component to open its Basic settings view.
  2. In the File Name field, browse to the input file, in this example person_table.txt.
  3. Click [...] next to the Edit schema field to describe the structure of the input file.
  4. 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.
  1. Double-click the tOracleOutput to open its Basic settings view.
  2. Define the Oracle connection details.
    In this example, the database connection details are stored in the Metadata folder of the Repository tree view.
  3. In the Table field, enter the name of the table into which the data will be written, in this example TABLE_PERSON.
  4. From the Action on table list, leave Default and from the Action on data list, select Insert to write the data.
  5. Click Sync columns to retrieve the schema from the preceding component.
  6. Click the Advanced settings tab to set the advanced parameters.
  7. 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

  1. Save your Job.
  2. Press F6 to execute it.

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