Re: Return value of _SEQ.CURRVAL to variable

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