Re: Return value of _SEQ.CURRVAL to variable

Hi Sybrand

Thanks for the quick reply.

I will explore using the returning statement in the sql.

The tables that i need to add the data to differ based on the input from the user. How would i go about using a procedure for this? Also is it safe to assume that if i call the Currval inside a transaction that i will be getting back the value i expected even if another session starts part way through the original session.

TIA Tony

Sybrand Bakker wrote:

> On 24 Sep 2006 13:34:24 -0700, "Tony B" <anthonybrough_at_googlemail.com>
> wrote:
>
> >Hi All
> >
> >I am new to oracle and would be frateful of any assistance.
> >
> >I have an asp page that adds data to several tables that all share a
> >common ID field (REGID)
> >
> >The database uses a sequence to get the NEXTVAL from the sequence
> >before adding a record to table 1.
> >
> >I then use tbl1_SEQ.currval to provide the value of the REGID for the
> >other tables. This all wrap in a transaction.
> >
> >What I need to do is to get the tbl1_SEQ.currval and assign it to a
> >variable so that i can use it after the transaction has been closed.
> >
> >I hope this is clear enough.
> >
> >Kind regards
> >
> >Tony
>
> First of all, I would recommend using the RETURNING clause of the
> INSERT statement, so you don't need to select the currval anymore
> insert into table bla values .... returning ... into
>
> Secondly, and related, I would recommend turning those statements in a
> procedure, and make sure this procedure has an OUT parameter,so you
> can return the REGID to the calling program
>
> The procedure would look like
> create or replace procedure transact(p_regid out number) is
> dum_regid number;
> begin
> insert into .. values(seq.nextval...) returning regid into dum_regid;
> ..
> ...
>
> p_regid := dum_regid;
> end;
> /
>
> Hth
>
> --
> Sybrand Bakker, Senior Oracle DBA
Received on Sun Sep 24 2006 - 16:19:29 CDT