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.
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.
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:
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.