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, ...);
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).
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 do I do an insert in oracle, and get the ID of the inserted row?
There are two possibilities. If you are using sequences for number generation (and you probably are, because Oracle doesn't have auto-num fields, like MySQL), you can get the value from the sequence, like this:
select SequenceName.currval into AVariable from dual
The other option is to use the returning
clause like this:
declare
AVariable int;
begin
insert into yourtable(columns)
values (values)
returning id into AVariable;
insert into anotherTable(columns)
values(AVariable, othervalues);
end;
How to get the generated id from an inserted row using ExecuteScalar?
Oracle uses sequences as for his identity columns, if we may say so.
If you have set a sequence for your table primary key, you also have to write a trigger that will insert the Sequence.NextValue or so into your primary key field.
Assuming that you are already familiar with this concept, simply query your sequence, then you will get your answer. What is very practiced in Oracle is to make yourself a function which will return an INT, then within your function, you perform your INSERT. Assuming that you have setup your trigger correctly, you will then be able to return the value of your sequence by querying it.
Here's an instance:
CREATE TABLE my_table (
id_my_table INT PRIMARY KEY
description VARCHAR2(100) NOT NULL
)
CREATE SEQUENCE my_table_seq
MINVALUE 1
MAXVALUE 1000
START WITH 1
INCREMENT BY 2
CACHE 5;
If you want to manage the auto-increment yourself, here's how:
INSERT INTO my_table (
id_my_table,
description
) VALUES (my_table_seq.NEXTVAL, "Some description");
COMMIT;
On the other hand, if you wish not to care about the PRIMARY KEY increment, you may proceed with a trigger.
CREATE OR REPLACE TRIGGER my_table_insert_trg
BEFORE INSERT ON my_table FOR EACH ROW
BEGIN
SELECT my_table_seq.NEXTVAL INTO :NEW.id_my_table FROM DUAL;
END;
Then, when you're inserting, you simply type the INSERT statement as follows:
INSERT INTO my_table (description) VALUES ("Some other description");
COMMIT;
After an INSERT, I guess you'll want to
SELECT my_table_seq.CURRVAL
or something like this to select the actual value of your sequence.
Here are some links to help:
http://www.orafaq.com/wiki/Sequence
http://www.orafaq.com/wiki/AutoNumber_and_Identity_columns
Hope this helps!
Returning the value of identity column after insertion in Oracle
Simply use the RETURNING clause.
For example -
RETURNING identity_id INTO variable_id;
Test case -
SQL> set serveroutput on
SQL> CREATE TABLE t
2 (ID NUMBER GENERATED ALWAYS AS IDENTITY, text VARCHAR2(50)
3 );
Table created.
SQL>
SQL> DECLARE
2 var_id NUMBER;
3 BEGIN
4 INSERT INTO t
5 (text
6 ) VALUES
7 ('test'
8 ) RETURNING ID INTO var_id;
9 DBMS_OUTPUT.PUT_LINE('ID returned is = '||var_id);
10 END;
11 /
ID returned is = 1
PL/SQL procedure successfully completed.
SQL>
SQL> select * from t;
ID TEXT
---------- --------------------------------------------
1 test
SQL>
Return sequence number on insert
There are two problems with your code:
You are using the wrong method to retrieve the generated keys result set. The generated keys result set can only be retrieved using
getGeneratedKeys()
(or at least, that is what the JDBC specification requires).You need to change your code to use
ps.getGeneratedKeys()
instead ofps.getResultSet()
The other problem is your code assumes non-standard generated keys behavior: your insert is not actually using a generated key in the sense the JDBC specification intends, as you are generating the identifier in the insert statement yourself (using
NEXT VALUE FOR seq1
), instead of the key being generated as a side-effect of the insert statement (eg by a identity column or a trigger).HSQLDB does not return a generated key in this situation as it does not consider
id
as a generated column. Instead, you need to either define the column as an identity column (and don't explicitly specify it in your insert), or alternatively, explicitly specify the column to return.For creating an identity column, refer to the HSQLDB documentation. To explicitly specify the column to return replace
conn.prepareStatement("<query>", PreparedStatement.RETURN_GENERATED_KEYS)
with either an index specification of the columns to return (that is
1
is the first column):conn.prepareStatement("<query>", new int[] { 1 })
or a column name specification of the columns to return
conn.prepareStatement("<query>", new String[] { "id" })
Your final code should be something like:
try (PreparedStatement ps = conn.prepareStatement(
"INSERT INTO orders(id, order_number) VALUES (NEXT VALUE FOR seq1, ?)",
new String[] { "id" })) {
ps.setString(1, order.getOrderNumber());
ps.execute();
try (ResultSet rs = stmt.getGeneratedKeys()) {
if (rs.next()) {
return rs.getLong(1);
}
}
}
Get the unique id the record got when inserting to a database table
Oracle 12 finally has built-in identity columns. So, if you are using the most recent version of Oracle, you can just use that. Read about it here.
In earlier versions, it is best to use a sequence. This guarantees uniqueness, even in a multi-threaded environment. I always implement triggers to update the id column. Here is an answer to a similar question, that explains how to do this.
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.
Related Topics
Why Are Batch Inserts/Updates Faster? How Do Batch Updates Work
How to Drop a Default Value or Similar Constraint in T-Sql
SQL Server Cumulative Sum by Group
Select Distinct from Multiple Fields Using SQL
How to Call an Oracle Stored Procedure from an Excel Vba Script
SQL Insert Without Specifying Columns. What Happens
The Alter Table Statement Conflicted with the Foreign Key Constraint
Slow Bulk Insert for Table with Many Indexes
Prevent Duplicate Values in Left Join
How to Implement a Keyword Search in MySQL
Partition by with and Without Keep in Oracle
Eliminate and Reduce Overlapping Date Ranges
How to Join Two Recordset Created from Two Different Data Source in Excel Vba
Calculate Difference Between 2 Dates in SQL, Excluding Weekend Days
Converting Select Results into Insert Script - SQL Server
Is This a Good Way to Model Address Information in a Relational Database