Retrieve Oracle last inserted IDENTITY
Well. Oracle uses sequences and default values for IDENTITY functionality in 12c. Therefore you need to know about sequences for your question.
First create a test identity table.
CREATE TABLE IDENTITY_TEST_TABLE
(
ID NUMBER GENERATED ALWAYS AS IDENTITY
, NAME VARCHAR2(30 BYTE)
);
First, lets find your sequence name that is created with this identity column. This sequence name is a default value in your table.
Select TABLE_NAME, COLUMN_NAME, DATA_DEFAULT from USER_TAB_COLUMNS
where TABLE_NAME = 'IDENTITY_TEST_TABLE';
for me this value is "ISEQ$$_193606"
insert some values.
INSERT INTO IDENTITY_TEST_TABLE (name) VALUES ('atilla');
INSERT INTO IDENTITY_TEST_TABLE (name) VALUES ('aydın');
then insert value and find identity.
INSERT INTO IDENTITY_TEST_TABLE (name) VALUES ('atilla');
SELECT "ISEQ$$_193606".currval from dual;
you should see your identity value. If you want to do in one block use
declare
s2 number;
begin
INSERT INTO IDENTITY_TEST_TABLE (name) VALUES ('atilla') returning ID into s2;
dbms_output.put_line(s2);
end;
Last ID is my identity column name.
How to get last ID from oracle database using SQL command?
Oracle tables with automatically assigned unique integer columns use sequence
objects to generate those unique values.
Look at the table definition or your INSERT
query. One of them will show you the name of a sequence object for the unique id column.
Then use the sequence, via sequence.nextval
, to create a unique id to INSERT
into a table.
Then immediately do SELECT sequence.currval FROM dual
to get the latest value, right after using the sequence to assign a unique value. For race-condition-safe operations do it placing the SELECT in a transaction with the INSERT.
This sequence stuff is often used in a series of INSERT operations something like this.
INSERT INTO person (person_id, given, surname, title)
VALUES (personid.nextval, 'Larry', 'Ellison', 'boss');
INSERT INTO phone (phone_id, person_id, type, value)
VALUES (phoneid.nextval, personid.currval, 'home', '555-1212');
INSERT INTO phone (phone_id, person_id, type, value)
VALUES (phoneid.nextval, personid.currval, 'office', '555-3434');
This gets us a person
row with two associated phone
rows. Notice the use of personid.currval
for both phone
rows.
How to get id of last row inserted in oracle
Some options:
If there is a natural key to go with your generated ID (you inserted columns that comprise a unique key), then selecting for the row with those key values will work.
You can use a RETURNING clause on an insert statement to return the ID column value, however this is not supported in all middleware connections.
If the trigger checks to see if you have already set the id field on insert and only fetches the sequence.nextvalue if you are passing in a null for that column, then do the ID sequence fetch yourself and include that value in your insert statement.
If the table was set up with audit columns (created_user / created_Date sort of things) then you can always select the last inserted row by you.
Retrieving identity of most recent insert in Oracle DB 12c
Taking what user2502422 said above and adding the python bit:
newest_id_wrapper = cursor.var(cx_Oracle.STRING)
sql_params = { "newest_id_sql_param" : newest_id_wrapper }
sql = "insert into hypervisor ( name ) values ('my hypervisor') " + \
"returning id into :python_var"
cursor.execute(sql, sql_params)
newest_id=newest_id_wrapper.getvalue()
Get ID of last inserted record in oracle db
Use the PL/SQL RETURNING
clause:
insert into mytable (...) values (...)
returning id into v_id;
Oracle get id of inserted row with identity always
The equivalent is
INSERT INTO dummy_schema.names (name) VALUES ('Random')
RETURNING id INTO :myvalue;
The mechanism how to pick up the returned ID depends on the host language (Java, PL/SQL, SQL*Plus etc).
Inserting into Oracle and retrieving the generated sequence ID
Expanding a bit on the answers from @Guru and @Ronnis, you can hide the sequence and make it look more like an auto-increment using a trigger, and have a procedure that does the insert for you and returns the generated ID as an out parameter.
create table batch(batchid number,
batchname varchar2(30),
batchtype char(1),
source char(1),
intarea number)
/
create sequence batch_seq start with 1
/
create trigger batch_bi
before insert on batch
for each row
begin
select batch_seq.nextval into :new.batchid from dual;
end;
/
create procedure insert_batch(v_batchname batch.batchname%TYPE,
v_batchtype batch.batchtype%TYPE,
v_source batch.source%TYPE,
v_intarea batch.intarea%TYPE,
v_batchid out batch.batchid%TYPE)
as
begin
insert into batch(batchname, batchtype, source, intarea)
values(v_batchname, v_batchtype, v_source, v_intarea)
returning batchid into v_batchid;
end;
/
You can then call the procedure instead of doing a plain insert, e.g. from an anoymous block:
declare
l_batchid batch.batchid%TYPE;
begin
insert_batch(v_batchname => 'Batch 1',
v_batchtype => 'A',
v_source => 'Z',
v_intarea => 1,
v_batchid => l_batchid);
dbms_output.put_line('Generated id: ' || l_batchid);
insert_batch(v_batchname => 'Batch 99',
v_batchtype => 'B',
v_source => 'Y',
v_intarea => 9,
v_batchid => l_batchid);
dbms_output.put_line('Generated id: ' || l_batchid);
end;
/
Generated id: 1
Generated id: 2
You can make the call without an explicit anonymous block, e.g. from SQL*Plus:
variable l_batchid number;
exec insert_batch('Batch 21', 'C', 'X', 7, :l_batchid);
... and use the bind variable :l_batchid
to refer to the generated value afterwards:
print l_batchid;
insert into some_table values(:l_batch_id, ...);
Related Topics
Rails 3 Activerecord Query Using Both SQL in and SQL or Operators
Query Several Nextval from Sequence in One Statement
Oracle: Function Based Index Selective Uniqueness
SQL Sort Order with Null Values Last
Oracle SQL Syntax: Quoted Identifier
Some Sort of "Different Auto-Increment Indexes" Per a Primary Key Values
What Are the Uses for Cross Join
How to Find a Default Constraint Using Information_Schema
Add Unique Constraint in SQL Server 2008 Gui
Calculating Percentages with Group by Query
Setting Up Rails to Work with SQLserver
Where Does the Practice "Exists (Select 1 from ...)" Come From
Select Closest Numerical Value with MySQL Query
Is There a Quick Way to Check If Any Column Is Null
SQL Server Equivalent of Wm_Concat Function
Select One Row Per Index Value with Max Column Value