| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: How to get the current value of a sequence?
A copy of this was sent to jojo7777_at_my-deja.com
(if that email address didn't require changing)
On Thu, 09 Dec 1999 10:24:04 GMT, you 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.currval, 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.currval
>FROM dual");
>
>
>--> Error message: "The sequence sequ is not declared in actual session"
> (or something similar)
>
Please don't use the SYS schema for anything. It is 'special'. get your tables out of it and create your own account to develop in....
that aside, you do not say if the "select currval" follows an "insert nextval". currval is SESSION specific -- it returns the current value of YOUR last inserted sequence. not the 'current' value of the sequence database wide (which is not necessarily known). The following shows the defined behaviour of nextval/currval:
tkyte_at_8.0> select s.currval from dual;
select s.currval from dual
*
tkyte_at_8.0> insert into t values ( s.nextval );
1 row created.
tkyte_at_8.0> select s.currval from dual;
CURRVAL
2
Until I use NEXTVAL in a session, currval is undefined. You can look at the ALL_, DBA_, USER_ sequences views. They will show you approximately the last 'currval' database wide but they are affected by the caching size and such. for example:
tkyte_at_8.0> select last_number from user_sequences where sequence_name = 'S';
LAST_NUMBER
21
tkyte_at_8.0> select s.nextval from dual;
NEXTVAL
3
tkyte_at_8.0>
my cache size is the default of 20 -- so 21 appears to be the 'currval' in the database but in my session I get 3 as the nextval. the database will report back in increments of 20 always in my case.
>The same error message is returned when I execute the query directly via
>SQL+ Monitor.
>
>
>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.
--
See http://osi.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 Thu Dec 09 1999 - 07:07:26 CST
 |
 |