This discussion is archived
1 2 3 4 Previous Next 59 Replies Latest reply: Sep 24, 2010 5:29 AM by 635471 RSS

Sequence Issue

170763 Newbie
Currently Being Moderated
I have a question for all the Oracle GURU's out there. I have the following scenario, I will appreciate any help


I have like 100 different sequences. What I want to do is, I want to write a function which take the Sequence Name and the Number of Sequence Numbers required as parameters,

eg
select SEQUENCE_FUNCTION('my_sequence', 1000) from dual;

What this function should return is a list of next 1000 numbers from the sequence my_sequence...for example, if the sequence currval is 1000, it gives me back 1001 till 2000. Now the tricky part, I want this function to be in the Database, and what I want is, if two different sessions call this function concurently, then the one of the sessions block the other from getting the numbers from the sequence till it gets its 1000 numbers, and then the second session can get the numbers

eg session one at 10:00:00 AM where currval is 1000
SELECT SEQUENCE_FUNCTION('my_sequence', 1000) from dual
Returns
1001 to 2000

session two at 10:00:01 AM
SELECT SEQUENCE_FUNCTION('my_sequence', 1000) from dual
Returns
Wait for the first session to finish and then grab the numbers from the sequence to return 2001 to 3000.

I have tried all kind of things, including Bulk Collection and making sure the the first sessions's process dosen't end till all the numbers are grabbed, the session 2 still manage to get the sequence numbers whihc it shouldn't get...what I am getting is as following

Session 1 function returns 1001 to 1200, then 1201, 1203, 1205 etc and goes beyound 2000
Session 2 function returns 1202, 1204, 1206 etc

I will really appreciate any help regrading this issue. Thanks again for lookign at my issue.
  • 1. Re: Sequence Issue
    munky Guru
    Currently Being Moderated
    Whay are you trying to do this? There may be a better way to meet the actual requirement...

    Cheers

    Ben
  • 2. Re: Sequence Issue
    170763 Newbie
    Currently Being Moderated
    Honestly speacking, I have been thinking of other ways, buat I guess this is exactl what the client wants, I will really appreciate help here.
  • 3. Re: Sequence Issue
    munky Guru
    Currently Being Moderated
    Hi

    I think it may be the time to tell the client that they are wrong. I could tell you how to do it, but it sounds like terrible design. One of the major benefits of Oracle is how it handles locking and concurrency. You want to take that away - sorry, this just sounds like an awful idea!

    Cheers

    Ben
  • 4. Re: Sequence Issue
    ToonKoppelaars Employee ACE
    Currently Being Moderated
    1) You can use dbms_lock (application locks) to serialize calls to your function. First line of code in your function would be "get some application lock in X- mode, with willing-to-wait some y seconds". Last line in your function would be to release that lock.

    2) Do you intend to use underlying real Oracle sequences? If yes, why? Do these sequences get accessed via other code also? If not, then you don't need sequences at all. Just have some table with a row per 'sequence-name', and a 'currval' of that sequence. In this case you don't need dbms_lock either: just select for update (with a WAIT-clause) the specific sequence row and update+commit the currval.
  • 5. Re: Sequence Issue
    170763 Newbie
    Currently Being Moderated
    Basically I am trying to replicate a PostGres Advisory Lock in Oracle. Do we have anything like that in Oracle World. I am involved in a conversion of a PostGres Application to Oracle....Its been working in PostGres...and I really think that if PostGres can handle it, then Oracle shold be able to handle it.
  • 6. Re: Sequence Issue
    ToonKoppelaars Employee ACE
    Currently Being Moderated
    Quickly looked them up on some Postgres documentation:
    PostgreSQL provides a means for creating locks that have application-defined meanings. These are called advisory locks ...
    In Oracle-land these would be locks you can create via the DBMS_LOCK package.
    Not 100% sure though, due to the references to MVCC in that part of the documentation...
  • 7. Re: Sequence Issue
    munky Guru
    Currently Being Moderated
    >
    ...and I really think that if PostGres can handle it, then Oracle shold be able to handle it.
    >
    I think you're somewhat missing my point. What is the business/application requirement that needs this? There is probably a better way to meet that requirement in Oracle. I can see what you are trying to do - I am asking why you are trying to do that (what is the purpose)?

    Each RDBMS has different features and works in different ways. You should maximise the benefit of these features when writing applications for them rather than trying to replicate the way it is done in another database - or you will end up getting stung or at least minimising the the benefits of the DB.

    Cheers

    Ben
  • 8. Re: Sequence Issue
    BluShadow Guru Moderator
    Currently Being Moderated
    mkmunir wrote:
    Basically I am trying to replicate a PostGres Advisory Lock in Oracle. Do we have anything like that in Oracle World. I am involved in a conversion of a PostGres Application to Oracle....Its been working in PostGres...and I really think that if PostGres can handle it, then Oracle shold be able to handle it.
    Ingres can do read locks on records that are queries. Does that mean Oracle should be able to handle it? No. Oracle is not Ingres. Oracle is also not PostGres, or SQL Server, or MySQL. Oracle works in it's own way and in a way that works well for Oracle. Rather than trying to translate one technology directly to another at such an exact techological level without consideration for the differences in the technologies, will leave you with a poorly designed system. You would be better to look at what the business/application logic is supposed to be doing and consider how Oracle already does it from it's own methods.
  • 9. Re: Sequence Issue
    170763 Newbie
    Currently Being Moderated
    I do understand that Oracle is not other databases, and that Oracle has its own way of doing things....having said that, can some one please suggest a solution of what I am trying to achieve, as mentioned in my original post regarding sequences.
  • 10. Re: Sequence Issue
    LokanathGiri Expert
    Currently Being Moderated
    Why don't you do manually. Just some idea !!!
    _Table_
    Session   Sequence_name    Start_val         end_val
    1         Seq_1            1000              2000
    If current values 1300 then display the mesage for session to sequence is in use by session 1. once it reaches 2000 it is open for session 2.

    ~Lokanath
  • 11. Re: Sequence Issue
    BluShadow Guru Moderator
    Currently Being Moderated
    mkmunir wrote:
    I do understand that Oracle is not other databases, and that Oracle has its own way of doing things....having said that, can some one please suggest a solution of what I am trying to achieve, as mentioned in my original post regarding sequences.
    We could, but it would be against all logic for us to provide you with details of how to do something that is clearly Wrong!
  • 12. Re: Sequence Issue
    6363 Guru
    Currently Being Moderated
    mkmunir wrote:

    I do understand that Oracle is not other databases, and that Oracle has its own way of doing things....having said that, can some one please suggest a solution of what I am trying to achieve, as mentioned in my original post regarding sequences.
    Oracle sequences don't work that way, they are designed to generate unique numbers efficiently and that is all. The only thing you should care about the result of an Oracle sequence is that it is unique, you should not assign any meaning to the resultant number. If you care about the resultant value you should not use a sequence. So what you are trying to achieve, is not possible using Oracle sequences.

    Also it is not clear what problem you are actually trying to solve in business terms. All you have described is that you are attempting to implement a feature from another database that Oracle does not provide, or have any use for.
  • 13. Re: Sequence Issue
    548849 Journeyer
    Currently Being Moderated
    Hi,

    Never tried such a thing. If you find a solution do post it.

    AJR
  • 14. Re: Sequence Issue
    584412 Journeyer
    Currently Being Moderated
    mkmunir wrote:
    I do understand that Oracle is not other databases, and that Oracle has its own way of doing things....having said that, can some one please suggest a solution of what I am trying to achieve, as mentioned in my original post regarding sequences.
    Hi mkmunir,
    It's been a while since I've posted here but the forum hasn't changed (that's a good thing). The guys here are not being awkward in not posting a solution for you, they are trying to do the best that they can for you, and that means not giving a process that is simply wrong for the dbms that you are using. If you would just open up a bit and let them know what it is that you are actually trying to achieve, they will likely be able to supply a very tidy and efficient method for achieving your task. Is there a reason that you will not tell people exactly what it is that you are trying to do?
1 2 3 4 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points