This content has been marked as final. Show 6 replies
A sequence is resolved by an agent, it can therefore be incremented only when the agent itself processes one record.
- 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.
It may be due to dupliacte values in field on which there might unique constraint in Database side.
Check for constarints on table.
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.
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.
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.