Problem using sequence in oracle stage.

[swamiar]

Hi,

I have two jobs that insert data into the same table in oracle database. We have a field in this table that gets its value from a sequence created in Oracle. I first run one job to insert into the table and next the other. But the sequence number does not continue from the last value that was present after the completion of the first job. Instead the sequence is automatically rounded off to the next nearest hundredth place. (ie if the sequence value was 1200105 at the end of the first job, the starting sequence for the next job is 1200200.) I use the SEQUENCE.NEXTVAL in the insert statement in the oracle stage. what could be the problem?

Thanks and regards.

Aravind

[DSguru2B]

Isn't that more of an oracle question than a Datastage. In Datastage we have Surrogate Key generator to perform the similar functionality.

[swamiar]

Hi,
But this happens only when i use datastage.

Regards,

Aravind

[ArndW]

Is your sequence not an integer?

[swamiar]

I have two jobs to insert records into the same table and the sequence field being a key field can not have duplicate values. This is the reason i use the sequence and not the surrogate key of datastage. There is a possibility of the jobs running parallel as well.

Regards,

Aravind.

[swamiar]

It is an integer field of length 10.

Regards,

Aravind

[sud]

It is an integer field of length 10.

Regards,

Aravind

But there is practically no way that ur sequence's current value will change unless nextval is called. Please check where u r using the sequence and actually how many times it is getting called.

[seanc217]

Also be aware if you are running Oracle on RAC this is how a sequence will not be sequential.

If order matters...

create the sequence with nocache order options. Note however there is a performance penalty for this.

HTH