Warning: Illegal string offset 'ssb_og_tags' in /home/content/10/4632510/html/wp-content/plugins/simple-social-buttons/simple-social-buttons.php on line 1466


The blog for Oracle Data Integrator ( ODI )


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


Call the sequence in the respective column using this format



TEMP                    –  SCHEMA_NAME


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


run your interface and check for data.


ODI Sequence

Create an ODI sequence with the required option


Map the column and the sequence with the ODI_SEQUENCE as




  • 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



  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.



  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

    And i have to mapped as below for Target Column.


  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?


    • 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
    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 *.

This site uses Akismet to reduce spam. Learn how your comment data is processed.