Sequence trouble in V6

[I'm cross-posting this to comp.databases.theory because I'm questioning
the semantics of SEQUENCEs: nevertheless, the following is OracleV6-specific]
[also I raise the issue of in-application caching, and of support for it]

I've read the manuals :-) and I'm still having trouble with SEQUENCES.

Essentially, I need to be able to discover the last number which was generated from a given sequence. This question is well-defined, since:

  When a sequence number is generated, the sequence is incremented,   independent of the transacton committing or rolling back. ... Two   users will never see the same sequence number generated by the same   sequence. [from Oracle's "SQL Language Reference Manual Version 6.0"]

[They oughta say "unless the sequence CYCLEs" but they aren't that fussy
about accuracy :-( ]

There are two potential ways of getting this value from a sequence 'SEQ1':

  SQL> select SEQ1.CURRVAL from DUAL;
  ERROR:
  ORA-08002: SEQ1.CURRVAL is not yet defined in this session

which is no use unless I've already drawn a sequence number "in the current session", e.g.

  SQL> select SEQ1.NEXTVAL from DUAL;   

     NEXTVAL

           3   

  SQL> select SEQ1.CURRVAL from DUAL;   

     CURRVAL

           3

and

  SQL> select LAST_NUMBER from ALL_SEQUENCES where SEQUENCE_NAME='SEQ1';   

  LAST_NUMBER

           21

which seems to bear more relation to the quantity of sequence numbers put in the cache rather than to those actually generated by explicit user request.

The semantics of Oracle V6 SEQUENCEs leave something to be desired IMHO.

I might work around the problem by arranging that my application discards, at the beginning of each "session", a number from every sequence to which it might need to refer, but I am using sequences as counters to denote table revision levels (each time my application adds or deletes a tuple from a table, it draws a number from the sequence), and I do not want to increment these counters unnecessarily (it will undermine in-application caching which depends on these numbers).

Unless I am missing something, I am inclined to be indignant and depressed at the lack of care which has gone into the specification of V6 sequences, i.e. they don't do what I want and IT'S NOT FAIR!

My applications cache data because of the latency of embedded SQL calls (the caching which the Oracle Kernel performs so admirably is of no help here), and periodically (at the beginning of each transaction) they distrust their cache, and compare the kernel's "current" sequence number with that corresponding to the cached data. If Oracle has moved onto a new number, they recache the data. I am about to try marginal recaching (i.e. fetching only the changes: this requires sequence-stamping every tuple: a price I am well prepared to pay, but not until sequences behave sensibly ...).

I wish that Oracle maintained update sequence numbers for all tables, much as Unix maintains time-of-last-modification for files (although timestamps with a granularity of one second are inadequate now that we can hit a file many times in a single second ...).

Please don't question my need to cache Oracle data in the application, just *believe* it!

I've tried creating sequences with cache sizes of 0 or 1, but that ain't allowed:

  SQL> create sequence SEQ1 cache 1;
  create sequence SEQ1 cache 1
  *
  ERROR at line 1:
  ORA-04010: the number of values to CACHE must be greater than 1

Can anyone suggest a workaround?

Failing that, does anyone sympathise :-)

Paul

  __   __    Paul Singleton (Mr)           JANET: paul_at_uk.ac.keele.cs
 |__) (__    Computer Science Dept.        other: paul_at_cs.keele.ac.uk
 |  .  __).  Keele University, Newcastle,    tel: +44 (0)782 583477 << NEW for
             Staffs ST5 5BG, ENGLAND         fax: +44 (0)782 713082     1993