Re: How to get the current value of a sequence?

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_at_nnrp1.deja.com>,   jojo7777_at_my-deja.com wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Dec 11 1999 - 12:31:15 CET