Auto generate sequence numbers in Target tables using Oracle sequence

·         Create a Target table with below structure by adding sequence column

    

CREATE TABLE "TARGET"."EMP_SEQ"

    (          

    "SEQ_NO" NUMBER,

    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,

    "ENAME" NVARCHAR2(10),

                "JOB" NVARCHAR2(9),

                "MGR" NUMBER(4,0),

                "HIREDATE" DATE,

                "SAL" NUMBER(7,2),

                "COMM" NUMBER(7,2),

                "DEPTNO" NUMBER(2,0),

                 PRIMARY KEY ("EMPNO")

     );

·         Create oracle sequence to generated the sequence values

CREATE SEQUENCE SEQ_NO

START WITH 1

INCREMENT BY 1;

·         Create Trigger to assign the values from sequence to SEQ_NO column.

CREATE OR REPLACE TRIGGER SEQ_NO

BEFORE INSERT ON EMP_SEQ

FOR EACH ROW

BEGIN

SELECT SEQ_NO.nextval INTO :NEW.SEQ_NO  FROM dual;

END;

·         Creating a mapping as below after importing required source and Target definitions.

  

·         Target table field SEQ_NO has the Oracle generated Sequence value in table. Below screen shot