Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Sequence Question

Re: Sequence Question

From: David Siver, Sr. <siver_at_usgs.gov>
Date: Wed, 2 May 2001 19:07:59 GMT
Message-ID: <3AF05B0F.FE8D9C61@usgs.gov>

add the following to your stored procedure:

    select seq.nextval into pk_tmp
    from dual;

Now you have your primary key for the record you are working with.

Jon Strauss wrote:

> 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 - 14:07:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US