Why don't you create a job within the oracle server that recreates your
sequence every day ?
GDN
Rafal Pietral <rafal_at_polcard.com.pl> wrote in article
<374A7BB6.D1694CCD_at_polcard.com.pl>...
> I fell into a similair problem. But to my best knowlage I couldn't use
> SEQUENCE. The problem is the following, I have:
> table (
> today DATE,
> runno NUMBER
> )
> And I also have :
> UNIQUE INDEX on table (today,runno)
>
> The reason why I cannot use SEQUENCE is that I don't know how to reset it
> every day - atomicaly. The only way I actually was able to reset sequence
> was to drop it and create it again, but this unfortunately cannot be
> protected by transaction comit (as far as I know).
>
> So I ended up in a small "increnent" program like in Martin example, with
> small disadvantage, that sometimes the comit may fail - and I have to
> rollback and start scenario again.
>
> Anybody knows a better way?
>
> -R
>
> Martin Hepworth wrote:
>
> > hi_wean_at_my-dejanews.com wrote:
> > >
> > > Hi there,
> > >
> > > I've a table, tbl_runno as following:
> > >
> > > appln_type VARCHAR2(10)
> > > runno NUMBER(5)
> > >
> > > Here are the scenarios,
> > > 1. Open a Oracle session, logon as 'user1'
> > > 2. SELECT runno FROM tbl_runno; return as 5
> > > 3. UPDATE tbl_runno SET runno = runno + 1; should be = 6
> > > 4. Do not COMMIT till all the transactions are successfully executed.
> > > 5. At the same time (1 second later), another Oracle session, logon
as
> > > 'user1' as well
> > > 6. SELECT runno FROM tbl_runno; return as 5 still.
> > >
> > > Questions:
> > > 1. How can the 2nd user get runno = 6, when the 1st user still has
not
> > > issue the COMMIT statement?
> > > 2. Is it any LOCK available in Oracle 8.0.5?
> > >
> > > Can anyone help me on these? Thank ou very much!
> > >
> > > Best Regards
> > > HW
> > >
> > > --== Sent via Deja.com http://www.deja.com/ ==--
> > > ---Share what you know. Learn what you don't.---
> >
> > HI
> > You might want to use a 'sequence' for this sort of thing, rather than
> > relying on your own critical sections to handle this.
> >
> > Martin
>
>
Received on Tue May 25 1999 - 15:21:13 CDT
