Re: Return value of _SEQ.CURRVAL to variable

Tony B wrote:
> 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

1. Do not top post. Scroll to the bottom to reply.
2. CURRVAL will return what you expect. But before you go to procedural code make sure you can't accomplish what you need with dynamic SQL. -- Daniel Morgan University of Washington Puget Sound Oracle Users Group