6 Replies Latest reply on Sep 23, 2013 3:42 PM by ParthD

    ODI 11G sequence is not working properly

      Hello All,

      I am trying to use sequence of ODI 11G.

      In the target table thei field is Primary Key where the Sequence is loaded.
      I hve enabled the staging area different from target.
      Following KMs are used:
      KM :LKM SQL to SQL
      IKM :IKM SQL to SQL Control Append
      CKM: CKM Oracle
      Sequence NAME IS SERIAL_NO.
      Type: Standard
      Increment By:1

      The error I am receiving is :
      ODI-1228: Task ODIEE_DB_TO_DB_FUNC_SEQ_INTERFACE (Integration) fails on the target ORACLE connection ODIEE_TGT_DS_PHY.
      Caused By: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (ODIEE_TGT.SYS_C008406) violated

      The coee has been generated is :
      insert into ODIEE_TGT.EMP_REC

      select     FIRST_NAME,
      from     ODIEE_TGT.I$_EMP_REC

      Can some one please help me to resolvew the issue.
        • 1. Re: ODI 11G sequence is not working properly
          Sutirtha Roy
          A sequence is resolved by an agent, it can therefore be incremented only when the agent itself processes one record.
          For example:
          - In the SQL statement insert into fac select :NO_FAC_NEXTVAL, date_fac, mnt_fac the sequence value will be incremented only once, even if the SQL statement processes 10,000 rows, because the agent does not process each records, but just sends the command to the database engine.
          - To increment every row, the data must transit via the agent. To do this, use the SELECT/INSERT syntax specific to Oracle Data Integrator:
          SELECT date_fac, mnt_fac /* on the source connection */
          INSERT into FAC (ORDER_NO, ORDER_DAT, ORDER_AMNT) values (:NO_FAC_NEXTVAL, :date_fac, :mnt_fac) /* on the target connection */

          Tips for Using Sequences
          To make sure that a sequence is updated for each row inserted into a table, each row must be processed by the Agent. To make this happen, follow the steps below:

          Make the mapping containing the sequence be executed on the target.

          Set the mapping to be active for inserts only. Updates are not supported for sequences.

          If you are using an "incremental update" IKM, you should make sure that the update key in use does not contain a column populated with the sequence. For example, if the sequence is used to load the primary key for a datastore, you should use an alternate key as the update key for the interface.

          If using Oracle Data Integrator sequences with bind syntax (:<SEQUENCE_NAME>_NEXTVAL), you must configure the data flow such that the IKM transfers all the data through the agent. You can verify this by checking the generated integration step in Operator . It should have separate INSERT and SELECT commands executed on different connections, rather than a single SELECT...INSERT statement.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
          • 2. Re: ODI 11G sequence is not working properly
            Hi ,
            It may be due to dupliacte values in field on which there might unique constraint in Database side.
            Check for constarints on table.
            • 3. Re: ODI 11G sequence is not working properly

              Thanks for the reply.

              I have executed all the steps except the last one. I am not sure how to configure the KM for IKM so that select and insert can be executed on different connections.

              The job is failing with the same error still now.
              • 4. Re: ODI 11G sequence is not working properly
                Bhabani Ranjan Mahapatra
                Let answer your 1st question.Its very simple.When ever you open your KM steps you must find "command on source" and "command on target". If you will write select query in source side and insert query in target side, then every time source query fire and target query inserts it untill the end.
                But i dont think its related to your KM.Its problem with your Target table.

                I think there are more constraints on your target except the primary key where sequence is loaded.If you have more keys on your target you mark the same keys in ODI level also.Second thing you make the sequence as insert only in odi map not update.

                • 5. Re: ODI 11G sequence is not working properly
                  if u want pass the sequence to primary key column ,in odi 11g we have a option native sequence.

                  process for creating native sequences:

                  first we need to create sequence in rdbms what your using.

                  create sequence command like :create sequence AP_seq start with 1
                  increment by 1
                  max value 5000

                  after creation of sequence in rdbs.
                  go to project tab:drill down ->folders->
                  sequences->right click>create new sequence->third option native sequence

                  give name->select your context and logical schema it will automatically display your db sequences.select your sequence.

                  and in interface mapping you can give this sequence next value directly to primary key column,you can put execution in staging area only.(ap_seq.nextval)

                  it may be helpful to u.

                  keep mark helpful if it is helpful to you.

                  • 6. Re: ODI 11G sequence is not working properly

                    @904205: ur sure your method works? I tried but I cannot run my interface ..please check this question. https://forums.oracle.com/thread/2585018