Facing an issue in accessing a sequence concurrently

Rajendra Sowani

Working on the order integration using staging schema between ERP and CRM.

Facing an issue :- Used one procedure to integrate the data from CRM to ERP and which is being called in two different order type simultaneously. In this procedure I have used a sequence which will be a primary key. The logic written is 1. fetching value from a sequence and 2. creating a record in the table.

In the above scenario, it is being observed that some times it is getting same sequence no in both the jobs and hence the result of the same triggers to failure of the transaction.

Need help to resolve this issue.

Regards,

Rajendra

Billy Verreynne

Impossible for a sequence generator (Oracle object) to hand out the same sequence twice.

If it could, it would be a serious bug. And a known bug.

As this is not the case, and you have failed to provide any evidence to the contrary, the logical reaction is that you have a bug in your code - and you are misdiagnosing that bug as the same sequence reusing the same number more than once.

Hoek

In addition to Billy:

You should always provide your full database version. ( the result of: select * from v$version; )

There's a known issue with sequences on multi-node RAC:

Bug 12736053 : SEQUENCE GENERATING DUPLICATE NUMBER ON A 2-NODE RAC (db version 10.2.0.4)

If you have access to Metalink/MyOracleSupport, then do a search there as well.

But I'd put my $0.02 to Billy's reply and check your code.