How to get the current value of a sequence?

[jojo...@my-deja.com]

Hi to all,

I have a problem with sequences in Oracle 8i:

how can I get the current value (currvalue) of
a database sequence with a Java application?

(sys.sequ is declared as a sequence in the database)

The following Java code works properly:

java.sql.Connection conn;

java.sql.Statement stmt = conn.createStatement();

java.sql.ResultSet rset = stmt.executeUpdate("INSERT INTO sys.table1
VALUES (sys.sequ.currvalue, 2)");

This code results in a error message created by oracle:

java.sql.Connection conn;

java.sql.Statement stmt = conn.createStatement();

java.sql.ResultSet rset = stmt.executeQuery("SELECT sys.seq.currvalue
FROM dual");

--> Error message: "The sequence sequ is not declared in actual session"
(or something similar)

My problem:

I need the current value of the sequence to read it from the ResultSet
in a variable.

thanks in advance

Jojo

Sent via Deja.com http://www.deja.com/
Before you buy.

[Jose Luis Perez Garcia]

Hi,

The current value of a sequence can be obtained
using <sequence name>.currval

Jose Luis Perez
BARCELONA
SPAIN

jojo...@my-deja.com escribió:

[Roger Stapley]

select last_number from all_sequences where sequence_name ='&seq_name';

HTH

[pber...@my-deja.com]

Paul
The reason it doesn't work is that sequence_name.currval does not
exist UNTIL a sequence number from that sequence has been SELECTED
during the current session - hence the 'ORA-08002: sequence
REF_RFXSEQNO.CURRVAL is not yet defined in this session' error message.
Unfortunately, unless either the sequence is defined as NO CACHE, or
you can be sure the sequence has not been accessed during the current
session, 'SELECT last_number FROM all_sequences WHERE sequence_name = '
will not give an accurate result either. This is because last_number
will be incremented by the number of sequence numbers cached as soon as
a SELECT sequence_name.nextval has be executed; so at this point
last_number can be higher than currval.
On the other hand, if you are going to use the sequence number you
retrieve (as your code seems to indicate) you should really be
using .nextval and not .currval, since .currval does not increment the
sequence - if you use it to INSERT three records in a row you will get
the same value in all the records, not usually a desirable result.
Hope this helps,
Paul

In article <82o5hk$tb4$1...@nnrp1.deja.com>,