Re: Sequence Question

One last way to handle this kind of situation is to place whatever you want from the stored procedure into a global temporary table. This works as long as you can do all your processing in a single session and don't have to pass the information to other users. You can, however, store anything you want, as long as you set up your global temporary table to hold it.

Van

"Jon Strauss" <strauss_at_wpb.nuwc.navy.mil> wrote in message news:2001May2.165538.346_at_npt.nuwc.navy.mil...
> Thanks for all of the comments! Very interesting and helpful.
>
> "Jon Strauss" <strauss_at_wpb.nuwc.navy.mil> wrote in message
> news:2001May2.143457.345_at_npt.nuwc.navy.mil...
> > Hi,
> >
> > I'd like to create a stored procedure that inserts a new record into a
 table
> > using a sequence for the primary key. I'd then like to return the
 primary
> > key value in an out parameter. The only way I've found to do this is to
> > first use an insert command and the seq.NEXTVAL for the primary key.
 Then
> > use a select of seq.CURRVAL into the out parameter. The problem I see
 with
> > this is that it's theoretically possible for the seqence value to be
> > incremented (by someone else) between the time it is inserted into the
 table
> > and then selected into the out parameter. Ideally, I'd like to assign
 the
> > seq.NEXTVAL to a variable and then use that variable in my insert and
 also
> > for the out parameter. Does anyone know how to do this?
> >
> > Thanks,
> > Jon
> >
> >
>
>
Received on Wed May 02 2001 - 19:57:56 CDT