Getting the last inserted sequence value

This function will return the last generated sequence value for a given table. It will work both for Oracle 8i and 9i.

create or replace function
 getLastGeneratedId(cTableName in Varchar2)
 return number is
 v_result number;
 begin
 declare
     v_sqlstmt varchar2(200);
     begin
         v_sqlstmt := 'SELECT ' || cTableName || '_seq.currval from dual';
         execute immediate v_sqlstmt into v_result;
         return v_result;
     end;
 end getLastGeneratedId;
 /
 show errors

 create table junk (id int not null,name varchar2(100));
 create sequence junk_seq increment by 1 start with 1
 minvalue 1 nomaxvalue
 nocycle cache 20;
 
 insert into junk values (junk_seq.nextval,'test');
 
 select getLastgeneratedid('junk') from dual;

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.

column By format 999;
column Min format 999,999;
column Cache format 9999;
column Last format 999,999,999;
column Max format 999,999,999;
select s.sequence_name, s.min_value "Min", s.max_value "Max", s.increment_by
"By",
s.last_number "Last", s.cache_size "Cache", cycle_flag, o.created "Created"
from user_objects o, user_sequences s
where o.object_name = s.sequence_name
and o.object_type = 'SEQUENCE';

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:

SELECT sequence_name.CURRVAL from DUAL;

The third issue really makes the second 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:

ORA9I SQL> create sequence test_seq;

Sequence created.

ORA9I SQL> select test_seq.nextval from dual;

   NEXTVAL
----------
         1

ORA9I SQL> select test_seq.currval from dual;

   CURRVAL
----------
         1

Everything looks good!!! Now, in a completely different session, let's select CURRVAL without previously selecting NEXTVAL:

ORA9I SQL> select test_seq.currval from dual;
select test_seq.currval from dual
       *
ERROR at line 1:
ORA-08002: sequence TEST_SEQ.CURRVAL is not yet defined in this session

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.