Re: ORACLE-SQL : Sequence Problem (Need SQL GURUS)

    The only way I know of "resetting" a sequence is to kill it and recreate it. Obviously, that's pretty drastic. Since you mentioned that you could use PL/SQL, I would suggest using the 'for counter in 1 .. <p_limit> loop' structure where <p_limit> is the parameter value you mentioned. Put this in a stored procedure and you can call it from anywhere. If you do this, this implies that you have to either perform whatever steps you were going to do with those generated numbers inside that loop ( 'cause the next iteration of the loop will lose the last generated number from the loop ) or store those generated numbers in a PL/SQL table so that you can have them outside the loop when it finishes. From what I can figure, unless your problem is more complicated than I can see, this should suffice nicely for you. I hope this helps.

                                                                    Shane.

azhan98_at_tm.net.my wrote in message <6t7hha$e43$1_at_nnrp1.dejanews.com>...
>Please advice me on this problem......
>
>I create a sequence say s_mysequence
>The sequnce will increment its value by 1.
>From my understanding, sequnce will alway increase its value when called.
>(Or can we reset the value?).
>
>Here's the problem:
>How can I write the code (SQL or PL/SQL or SQL*PLUS)that will increase the
>sequence value until it match the value of other parameter value and reset
>its value back to 1.
>
Received on Wed Sep 09 1998 - 00:00:00 CEST