Re: sequence currval

A copy of this was sent to Mattias Malmgren <a2matmal_at_ulmo.stud.slu.se> (if that email address didn't require changing) On Sun, 12 Sep 1999 12:49:38 +0200, you wrote:

>Hello
>
>User's manual says:
>
>This example selects the current value of the employee sequence:
>SELECT empseq.currval
> FROM DUAL;
>
>I don'n understand. Way "FROM DUAL" ???
>

because a sequence is a database object, it must be selected from. Since you want but one value -- you select from DUAL, a one row table.

You can select empseq.currval from emp but then you would get a currval value for EACH row in emp-- you only care about getting the value so dual is appropriate.

>I have got a table with pimary keys. I insert into that tabel by:
> INSERT INTO flfraga (id,
> VALUES (flfraga_seq.NEXTVAL);
>
>Later in my stored procedure I what to access the flfraga_seq.CURRVAL
>and store it
>in a variable:
>
>select flfraga_seq.CURRVAL into k;
>

select flfraga_seq.currval into k FROM DUAL;

>Now, that doesn't work. Nor does select flfraga_seq.CURRVAL into k from
>dual;

what is the error?

>or select flfraga_seq.CURRVAL into k from flfraga;
>
>Must i first insert the flfraga_seq.CURRVAL into a special tabel and
>then select it????
>The I coud just as well use select max(id) into k from flfraga ;
>

no you cannot not, not in a multi user system anyway. others will have inserted rows and perhaps committed or not. depending on the state of the system you may see their values or yours. currval is YOUR last selected/inserted NEXTVAL. Its the way to get what you want.

> / Mattias

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Sep 12 1999 - 08:17:31 CDT