CODEWITHSUNDEEP
databaseoracle-database
Lahiru Chandima
Lahiru Chandima

Reputation: 24118

Oracle - How to get last sequence value used in current session

I have a table in which the id column gets its value from a sequence.

I am interacting with database using OCI.

After inserting a record to the table, I need to get the value of the sequence which was used when inserting the record (into to my process memory), which effectively is the most recent sequence value generated for the current session. According to this answer, it should be possible.

I found USER_SEQUENCES table which may be suitable for my purpose, but I am not sure whether I can use it because it has sequence data about current user. What I need is sequence data of current session. I have multiple concurrent database sessions which use same user id and password.

Upvotes: 1

Views: 9534

Answers (4)

ibre5041
ibre5041

Reputation: 5298

You should specify which development environment are you using. If you use JAVA, when you have problem, because JDBC assumes what DML statements have ONLY input bind parameters. The common trick is to wrap an INSERT statement into anonymous block and make it callable statement.

begin
 insert into t(id, x) valuse(t_seq.next_val, :x) returning in into :id;
end;

Such a callable statement will have one IN and one OUT parameter. Also JDBC 3.0 introduced it's own way ( getGeneratedKeys() method), but I'm not sure how it works with Oracle.

In case of OCI it works out-of-the-box. If I recall correctly in OCI you simply call OCIBindByName and you specify, that variable from returning clause is of type (INOUT). Then after execution you will find ID returned from a statement in that bind variable. It even works with bulk inserts, i.e you pass vector(s) of values to be inserted into the DB, and you get a vector of IDs generated.

I can not quickly find any OCI example. In case of OTL (OCI wrapper lib) it's here here. I think any OCI wrapper lib supports it. Also look at OCILIB, it's source code is easier to read.

Upvotes: 1

Chirag Mehta
Chirag Mehta

Reputation: 9

SEQ_NAME.CURRVAL as per #Stefan said, but in that case you have 1st time you must have NEXTVAL for the same sequence in current session, in case of having CACHE in sequence you can use #EntGriff option of return inserted ID to variable.

Upvotes: -1

EntGriff
EntGriff

Reputation: 875

you can make like this : 

  insert into testtable (ID, ...)
   values (schema.seq_testtable.nextval, ...)
  returning ID into currID;

you can read here about "returning" clause :

http://psoug.org/snippet/UPDATE-with-RETURNING-clause_604.htm

Upvotes: 3

Stefan Yordanov
Stefan Yordanov

Reputation: 666

You can use sequence_name.currval . Oracle documentation says:

Using Sequence Numbers with CURRVAL

To use or refer to the current sequence value of your session, reference seq_name.CURRVAL. CURRVAL can only be used if seq_name.NEXTVAL has been referenced in the current user session (in the current or a previous transaction). CURRVAL can be referenced as many times as necessary, including multiple times within the same statement. The next sequence number is not generated until NEXTVAL is referenced.

Upvotes: 2

Related Questions