How to get next sequence number in a stored procedure?

In a stored procedure I need to fetch the next value of a sequence
into a variable. What's the best method to do it?

Currently I do something like
select myseq.nextval into myvar from dummy_table;

dummy_table is (as it says) a dummy with one row.

I'm not happy with this solution, it looks like a hack. Directly
assigning myseq.nextval to a variable leaded to an error.


Ans:
Does your program really need to know the value ?

e.g. if you are inserting a parent and several children
with the pf/fk established through the sequence you
can use the sequence directly as:

    insert into parent (.... my_seq.nextval,....)
    insert into child (....., my_seq.currval...)
    insert into child (....., my_seq.currval...)
    insert into child (....., my_seq.currval...)

without ever storing the value locally.

BTW - at some stage you should consider changing
your dummy_table to 'dual' which is a table created
by the installation process for exactly this sort of
kludge processing - one row, one column (and in v8
it is even one of the X$ virtual tables).

--
Jonathan Lewis
 

Hosted by www.Geocities.ws

1