ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Sequence

Database Sequence

Create a database Sequence in Oracle.

create sequence test_sequence
minvalue 2
maxvalue 999
start with 2
increment by 2

Map your interface

image

Call the sequence in the respective column using this format

<SCHEMA_NAME>.<SEQUENCE_NAME>.NEXTVAL

TEMP.TEST_SEQUENCE.NEXTVAL

TEMP                    –  SCHEMA_NAME

TEST_SEQUENCE   –   SEQUENCE_NAME

  • Map it to  ‘Target
  • Use ‘Insert’ only Option

image

run your interface and check for data.

image

ODI Sequence

Create an ODI sequence with the required option

image

Map the column and the sequence with the ODI_SEQUENCE as

:ODI_SEQ_NEXTVAL

:<COLON>_<ODI_SEQUENCE>_NEXTVAL

image

  • Map it to  ‘Target
  • Use ‘Insert’ only Option
  • IKM SQL TO SQL APPEND  ( for Row by Row processing)

run the interface and check for the data

image

15 Comments

  1. can you help on this i have a requirment i have to maintain unique number for every 5000 records as a batch.

  2. Hi what is the maximum value an ODI sequence will reach upto?
    After reaching at the max value does it loops back and starts from 1 as DB sequence?

  3. format is #.nextval in odi 12c. isnt it?

  4. Hi,

    How to retrieve current value.

    Regards,

    Rabi

  5. Hello guys,
    I want to know how to make a logic of sequence number, it is running on CDC basis, every time generate a unique number. Whenever i execute the scenario it should not be start from 1 or 0, it incremented by 1 start when the last execution stop.

    Please light up on this issue or your valuable answer.

  6. Hi Experts,
    I really need an urgent help of whose answer I am not getting anywhere. So i thought the best source will be you.
    I am having column Account_Number which is having Duplicate values in Source.
    And I am having a primary Key Constraints on ACCOUNT_NUMBER in Target.
    So while Executing interface it gives Unique Constraint voilated.

    Now my requirement,In Target column Account_Number we have to append Sequence..
    For E.g
    Source table
    Account_Number column having following values
    Account_Number
    abcd
    abcd
    abcd
    abcd
    efgh
    efgh
    efgh

    And i have to mapped as below for Target Column.
    Account_Number
    abcd_1
    abcd_2
    abcd_3
    abcd_4
    efgh_1
    efgh_2

    Regards
    Prashant

  7. Hi Experts,

    I really need an urgent help of whose answer I am not getting anywhere. So i thought the best source will be you.

    I am having a ID column (primary key) in a table which I need to load with seq. generator so that the value for each record increases by 1.
    So every time the ETL is run value in the table should increase from 1 and again in the next load should start from 1.

    I created a seq generator and used it but the issue is for each run of the ETL, the value is same. For eg: For the first run the value is 1 for all records and 2 for all records of the second run. Where as I want it to be like below.

    1st Run

    ID Name
    1 a
    2 b
    c c

    2nd run.

    1 d
    2 e
    3 f

    • Hi…

      What I can suggest is to create a procedure in ODI that drops the sequence and recreate it. Use the procedure as first step of your load package (or load process).

      Make sense?

      Cezar

    • Hi, I having the same problem, did you find already a solution

  8. hi,
    I wnt to use ODI sequence in my project. whether the sequence number created will be continuous with every execution or it will start from the first for every execution. If it continues the sequence number for every execution of the interface, is there a way to restart the sequence for every interface execution.

  9. hi….
    i want to use a user defined function for converting null to default value.i m using function as
    nulltodefault($(pal.lastname),$(var)).i
    i hav to use this interface and a variable (var) in a package where var contains default value.how to do that?
    what to write in function code and at target mapping?

  10. Hi,

    I have a question. In source table i have 50 rows and i am processing all 50 rows at once to target. Now if first row has insert and 10th row has update to same id.

    When i run ODI it fails some time as it tries to process update first insert latter. Reason was it doesn’t follow any sequence when we select commit after 50 rows or so.

    Any solution to this?

Leave a Reply

Required fields are marked *.