Re: question about sequences.

On Sat, 15 Feb 2003 18:17:04 GMT, "Ryan" <rgaffuri_at_cox.net> wrote:

>I got a trick question in an interview and I dont know if what I said was
>write. It was a written exam so I couldnt ask.
>
>Someone does
>
>select sequence.nextval
> from dual;
>
>you get two sequence numbers back. All I could think of was that someone
>inserted a second row into dual.
>
>Could any other reason lead to two sequence numbers being returned?
>
>

Hi Ryan,

Maybe the question was just awkwardly asked. I guess they wanted to test if you knew about both sequence.NEXTVAL and sequence.CURRVAL :

Nextval is returned from the statement. No doubt. And if you afterwards do :

select sequence.currval from dual;

You get the same value again. In that sense, you can say that you get 2 values at your service, *because* :

If you start a user session and have a sequence myseq and issue :

select myseq.currval from dual;

You get ORA-08002: Sequence MYSEQ.CURRVAL not yet defined.....

You have to invoke NEXTVAL by *first* calling

select myseq.nextval from dual;

and *then* you can do ;

select myseq.currval from dual;

So "select sequence.nextval from dual" does, make another value avilable : sequence.currval.

Interviewers trying to make tricky questions, are often tricking themselves instead....

• Kenneth Koenraadt