ORA OCI stage and Oracle sequence

AthiraM · Post by **AthiraM** » Wed Mar 07, 2007 10:52 pm

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

chulett · Post by **chulett** » Wed Mar 07, 2007 11:06 pm

How can we 'shed any light'? You've given us no clue what field or fields make up your PK constraint in that table.

If your Array Size is greater than 1, try setting it down to 1 and let us know if the error persists.

AthiraM · Post by **AthiraM** » Wed Mar 07, 2007 11:14 pm

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

chulett · Post by **chulett** » Wed Mar 07, 2007 11:31 pm

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?

saikir · Post by **saikir** » Thu Mar 08, 2007 3:00 am

Hi,

IF you have enabled 'Cycle' option during the sequencer creation then the sequence numbers would repeat. Say if the max value is 100, on reaching 100 the sequence would again start from 1... 100. In such a case, you would defnitely get the uniques constraint violation

Sai

kumar_s · Post by **kumar_s** » Thu Mar 08, 2007 3:45 am

Disable the Constraint for a while and load the data into table and check what is the actual data that been loaded (which were tyring to load previous time).

ray.wurlod · Post by **ray.wurlod** » Thu Mar 08, 2007 7:53 am

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.

chulett · Post by **chulett** » Thu Mar 08, 2007 7:59 am

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.

AthiraM · Post by **AthiraM** » Thu Mar 08, 2007 8:24 pm

Thankyou for the valuable inputs. I am checking with the sequence.

Athira

AthiraM · Post by **AthiraM** » Fri Mar 09, 2007 3:38 am

Its working.....

The destination table already had some data that was inserted w/o using sequence (for the primary key column) and when i tried to insert data using sequence it created all the mess.
once again all the inputs were really very valuable for a beginner.

Thanks a lot
Athira

kumar_s · Post by **kumar_s** » Fri Mar 09, 2007 3:40 am

Hi Athira, Its time again for you to change something in this post when the solution is arrived.