This function will return the last generated sequence value for a given table. It will work both for Oracle 8i and 9i.
Here is an example:
Reader Feedback
Mark G. writes: This tip has only limited value. It only works in a session that has used
the sequence to generate a value. It cannot be used for example from a
support person's account to see what others have been doing. Also, it only
works (as written) if the sequence name is the same as a table name, but
with "_seq" appended to the end. Another drawback is the dynamic PL/SQL
syntax. This is slower than standard (static) PL/SQL syntax.
Here is a query I use to do approximately the same thing. This works from
any session, not just a session that has referenced a particular sequence.
It could easily be modified to select the values for just one sequence.
Unfortunately for sequences that are cached, it will display the next
non-cached value. The actual last value is usually a bit lower. This could
be modified to select from the "all_objects" and "all_sequences" instead of
from the "user..." views. Then it would be necessary to add the line: "and
o.owner = s.sequence_owner" and it may be desirable to exclude those owned
by; SYS, SYSTEM, etc.
Brian P. writes: I believe that there are a few issues with this tip.
The first issue is that the stored procedure given
assumes that any sequence name will be of the form
"tablename_seq". This is bad practice in that it ties
a sequence to a specific table, when in fact Oracle
sequences (by definition) are not tied to any table. A
sequence can be used by many tables and a table can
utilize many sequences. I agree that this might be
"bone picking", but I just had to do it! ;)
The second issue is that it seems to be a lot of work
just to create a stored procedure to select CURRVAL
from the sequence. Why not just do this from a SQL
statement? Why not just issue:
The third issue really makes the sec...
To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
ond issue I
mentioned a moot point. The problem with selecting
CURRVAL is that ONE MUST SELECT NEXTVAL FIRST!!!!!! If
you have not selected NEXTVAL, you will receive an
error. For example, let's create a sequence and use it
in a session:
Everything looks good!!! Now, in a completely
different session, let's select CURRVAL without
previously selecting NEXTVAL:
Since I haven't select NEXTVAL, any of the sequence's
values are not yet defined for that session. So the
stored procedure given in the tip will only work if
one has previously selected NEXTVAL on that specific
sequence in that specific session. Otherwise, the
ORA-8002 error will be raised.
Ofer H. writes: No. The currval method works only after a nextval method was performed.
If you try to query currval without prior nextval, you will be prompt
with an error: ORA-08002: sequence STAM_SEQ.CURRVAL is not yet defined in this session. Try it.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.
