Forum Stats

  • 3,853,380 Users
  • 2,264,214 Discussions
  • 7,905,332 Comments

Discussions

Facing an issue in accessing a sequence concurrently

Rajendra Sowani
Rajendra Sowani Member Posts: 2 Blue Ribbon
edited Sep 6, 2013 9:11AM in SQL & PL/SQL

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

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,876 Red Diamond

    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
    Hoek Member Posts: 16,087 Gold Crown

    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.


This discussion has been closed.