SELECT IDENT_CURRENT(‘tablename’) in Oracle

Question

I'm new with PL/SQL and I need last inserted id in data table after insert statement.

Like on MS SQL SELECT IDENT_CURRENT(‘tablename’)

In SQL Server, if you want the ID of a row you just inserted you should use `scope_identity`. `ident_current` returns the last identity value across all sessions and all scopes. So you may be getting a value for an insert in another session. http://msdn.microsoft.com/en-us/library/ms175098.aspx — Shannon Severance, Sep 07 '10 at 22:10

score 8 · Answer 1 · answered Sep 07 '10 at 10:10

8

You can use the RETURNING clause:

insert into mytable(x,y) values (1,2) returning id into v_id;

This works well when there is a trigger that fills an id column, so you don't need the "select seq.currval from dual" thing.

answered Sep 07 '10 at 10:10

Erich Kitzmueller

36,381
5
80
102

Alexander Malakhov · Accepted Answer · 2019-09-04T12:20:52.230

4

Update Oracle 12c has introduced identity columns (search "identity_clause", first few hits will be schemes and then text description). See example in Lukas' answer.

There were no built-in autoincrement fields in Oracle (prior to 12c), you created them using sequences:

CREATE TABLE some_tab(
      rec_id      INTEGER,
      some_data   VARCHAR2(300)
);

CREATE SEQUENCE some_tab_seq;

CREATE OR REPLACE TRIGGER trig_BI
   BEFORE INSERT
   ON some_tab
   FOR EACH ROW
BEGIN
   IF :NEW.rec_id IS NULL
   THEN
      :NEW.rec_id := some_tab_seq.NEXTVAL ;
   END IF;
END;

Then in PL/SQL you can fetch current value of the sequence by

your_var := some_tab_seq.CURRVAL

Also in older version of Oracle you can't directly read NEXTVAL / CURRVAL into var and have to do:

SELECT some_tab_seq.CURRVAL
  INTO your_var
  FROM DUAL;

edited Sep 04 '19 at 12:20

answered Sep 07 '10 at 10:07

Alexander Malakhov

3,383
2
33
58

3

CURRVAL only works if we have previously selected a NEXTVAL from the sequence in the session. – APC Sep 07 '10 at 10:28
You can also look at select sequence_name, last_number from user_sequences, but concurrency and caching (especially on RAC) make this inexact. – Gary Myers Sep 07 '10 at 23:26
ALL_SEQUENCES.LAST_NUMBER: "Last sequence number written to disk. If a sequence uses caching, the number written to disk is the last number placed in the sequence cache. This number is likely to be greater than the last sequence number that was used." http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/statviews_2062.htm#i1588488 – Shannon Severance Sep 08 '10 at 00:03
@Gary, @Shannon: wouldn't it require extra grants for user ? Can't recall this now. @ammoQ: thanks! fixed. – Alexander Malakhov Sep 08 '10 at 01:05
@APC: +1, forgot about this, thanks. So, in new session my only option is USER_SEQUENCES ? – Alexander Malakhov Sep 08 '10 at 01:14
1

@Alexander Malakhov: I don't think all sequences would require extra grants, it's just going to include those sequences you have access to. DBA_SEQUENCES would require extra grants. And if the table is in the users schema, USER_SEQUENCES is available. I don't think using *_SEQUENCES is very meaningful. If you the sequence caches, what you read in LAST_NUMBER will often be a number that has not been handed out yet.If you don't use caching concurrency and performance take a hit, since every fetch of NEXTVAL requires the equivalent of an autonomous transaction, complete with a commit. – Shannon Severance Sep 08 '10 at 14:55
1

Oracle 12c has introduced the standard `IDENTITY` feature, so parts of your answer are outdated – Lukas Eder Jul 16 '19 at 08:54
@LukasEder thanks for noting it! Feel free to edit mine or post your own answer - I would definitely upvote it. I'm not using Oracle since 2012 and don't even have a sandbox to test anything. – Alexander Malakhov Jul 16 '19 at 12:16

score 2 · Answer 3 · answered Sep 07 '10 at 10:17

Oracle does not implement identity columns. It uses Sequences which generate unique numbers which can be used as PK values in any table.

So the expression IDENT_CURRENT('my_table') is best translated into MySequence.CURRVAL of the sequence feeding the table's PK.

Typically, you'd retrieve the inserted PK value in MS SQL by:

INSERT INTO MyTable ...
SELECT @PK = SCOPE_IDENTITY()

In Oracle, use the INSERT RETURNING clause to achieve similar functionality

DECLARE PK ...;
INSERT INTO MyTable
RETURNING TableID INTO PK;

score 1 · Answer 4 · answered Jul 16 '19 at 13:03

If you're using Oracle 12c IDENTITY columns, as I've mentioned in this blog post or on this other Stack Overflow answer, you could fetch all the current identity values of your schema with a single query:

with
  function current_value(p_table_name varchar2) return number is
    v_current number;
  begin
    for rec in (
      select sequence_name
      from user_tab_identity_cols
      where table_name = p_table_name
    )
    loop
      execute immediate 'select ' || rec.sequence_name || '.currval from dual'
      into v_current;
      return v_current;
    end loop;

    return null;
  end;
select *
from (
  select table_name, current_value(table_name) current_value
  from user_tables
)
where current_value is not null
order by table_name;
/

This query can obviously be filtered for table_name, or you just reused the content of the current_value() function above directly.

In comment to my post APC has noted *"CURRVAL only works if we have previously selected a NEXTVAL from the sequence in the session"*. is it still an issue in modern Oracle versions? — Alexander Malakhov, Jul 16 '19 at 13:36

SELECT IDENT_CURRENT(‘tablename’) in Oracle

4 Answers4