ORA OCI stage and Oracle sequence
Moderators: chulett, rschirm, roy
ORA OCI stage and Oracle sequence
Hi,
I am using a sequence in a user defined sql query to insert data into an Oracle table using ORA OCI stage.
But only a very few data is getting inserted saying "unique key constraint has violated", are there any issues using the sequence in a user defined sql in an ORA OCI ?
The job design
http://img104.imageshack.us/my.php?image=dsjob1sw5.jpg
The sql query used :
INSERT INTO ODB.VAS_ORDER_MASTER_NEW (VOM_ORDER_NO,VOM_CUSTOMER_EIN,VOM_SUBMITTED_DT,VOM_START_DT,VOM_ACTUAL_END_DT,VOM_ORDER_TYPE,VOM_MONTHLY_ALLOWANCE,VOM_CHC,VOM_STATUS_NO,VOM_REALLOC_ORDER_NO) VALUES (seq_order_no.nextval,:1,TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:4, 'YYYY-MM-DD HH24:MI:SS'),:5,:6,:7,:8,:9)
The error :
ORA1..Transformer_4: ORA-00001: unique constraint (ODB.VOM_ORDER_NO_PK1) violated
How can the unique key constraint be violated when a sequence is being used?
Very strange, i tried running the job 3 times, the first time it moved in 86 rows , the second time 43 and the third time 24.
Do shed some light on this.
Thanks
Athira
I am using a sequence in a user defined sql query to insert data into an Oracle table using ORA OCI stage.
But only a very few data is getting inserted saying "unique key constraint has violated", are there any issues using the sequence in a user defined sql in an ORA OCI ?
The job design
http://img104.imageshack.us/my.php?image=dsjob1sw5.jpg
The sql query used :
INSERT INTO ODB.VAS_ORDER_MASTER_NEW (VOM_ORDER_NO,VOM_CUSTOMER_EIN,VOM_SUBMITTED_DT,VOM_START_DT,VOM_ACTUAL_END_DT,VOM_ORDER_TYPE,VOM_MONTHLY_ALLOWANCE,VOM_CHC,VOM_STATUS_NO,VOM_REALLOC_ORDER_NO) VALUES (seq_order_no.nextval,:1,TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:4, 'YYYY-MM-DD HH24:MI:SS'),:5,:6,:7,:8,:9)
The error :
ORA1..Transformer_4: ORA-00001: unique constraint (ODB.VOM_ORDER_NO_PK1) violated
How can the unique key constraint be violated when a sequence is being used?
Very strange, i tried running the job 3 times, the first time it moved in 86 rows , the second time 43 and the third time 24.
Do shed some light on this.
Thanks
Athira
The primary key of the table is vom_order_no and the primary key contraint is named ODB.VOM_ORDER_NO_PK1, a sequence is used to populate it as in query below
INSERT INTO ODB.VAS_ORDER_MASTER_NEW (VOM_ORDER_NO,VOM_CUSTOMER_EIN,VOM_SUBMITTED_DT,VOM_START_DT,VOM_ACTUAL_END_DT,VOM_ORDER_TYPE,VOM_MONTHLY_ALLOWANCE,VOM_CHC,VOM_STATUS_NO,VOM_REALLOC_ORDER_NO) VALUES (seq_order_no.nextval,:1,TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:4, 'YYYY-MM-DD HH24:MI:SS'),:5,:6,:7,:8,:9)
The array size is set to 1.
Athira
INSERT INTO ODB.VAS_ORDER_MASTER_NEW (VOM_ORDER_NO,VOM_CUSTOMER_EIN,VOM_SUBMITTED_DT,VOM_START_DT,VOM_ACTUAL_END_DT,VOM_ORDER_TYPE,VOM_MONTHLY_ALLOWANCE,VOM_CHC,VOM_STATUS_NO,VOM_REALLOC_ORDER_NO) VALUES (seq_order_no.nextval,:1,TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:4, 'YYYY-MM-DD HH24:MI:SS'),:5,:6,:7,:8,:9)
The array size is set to 1.
Athira
Then the issue is something you haven't mentioned yet.
How about posting the sequence object definition? Curious what some specific attributes are:
Increment By
Max Value
Cycle
Fun stuff like that... but please post them all. I'm also curious about your 'very strange' remark - when you ran the job 3 times were you expecting it to process 86 rows each time? Are there any constraints on your transformer output link?
![Confused :?](./images/smilies/icon_confused.gif)
How about posting the sequence object definition? Curious what some specific attributes are:
Increment By
Max Value
Cycle
Fun stuff like that... but please post them all. I'm also curious about your 'very strange' remark - when you ran the job 3 times were you expecting it to process 86 rows each time? Are there any constraints on your transformer output link?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
My guess is that the sequence and the table are not synchronized. There's nothing in Oracle to force them to be synchronized.
Someone has added rows to the table without using the sequence.
You need to re-synchronize (re-initialize) the sequence using SELECT MAX(key)+1 FROM tablename as the value.
Someone has added rows to the table without using the sequence.
You need to re-synchronize (re-initialize) the sequence using SELECT MAX(key)+1 FROM tablename as the value.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
That's where I was trying to get to, Ray. Eventually.
Just because you use a Sequence doesn't magically mean you can't have duplicates. That could be the case if the target table started off life empty and you never had any issues with the Sequence itself, like some kind soul resetting it for you. But what if a scattering of records already exist? Or others are inserting records as well but not using the sequence? All kinds of ways to goof this up.
No different from any other surrogate key generation mechanism, the sdk routines for example.
![Wink :wink:](./images/smilies/icon_wink.gif)
Just because you use a Sequence doesn't magically mean you can't have duplicates. That could be the case if the target table started off life empty and you never had any issues with the Sequence itself, like some kind soul resetting it for you. But what if a scattering of records already exist? Or others are inserting records as well but not using the sequence? All kinds of ways to goof this up.
No different from any other surrogate key generation mechanism, the sdk routines for example.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers