[sql] Inserting into Oracle and retrieving the generated sequence ID

I have a handful of raw SQL queries for SQL Server which use SCOPE_IDENTITY to retrieve the generated ID for a specific INSERT immediately after that INSERT occurs all in one execution…

INSERT into Batch(
BatchName,
BatchType,
Source,
Area
) Values (
@strBatchName,
@strType,
@strSource,
@intArea
);

SELECT SCOPE_IDENTITY() BatchID;

The question is:

What’s the best way to do that for an Oracle database?

Can this be done on Oracle through standard SQL or do I have to switch this to use a stored procedure and place something similar in the body of the stored proc?

If it must be a stored proc, then what is the de-facto standard way for retrieving the last generated sequence number, taking care to consider there will likely be overlapping executions on multiple threads so this mechanism will need to retrieve the right generated ID and not necessarily the absolute last generated ID.

If two execute simultaneously then each must return the correct generated ID from each respective call. Notice I’m not using SQL Server's “@@IDENTITY” because of that multithreaded nature of the calls.

I would rather keep it as raw SQL if possible since that’s much easier for me to manage across platforms (single file containing each platform's SQL block separated by DBMS identifying tags). Stored procs are a bit more work for me to manage, but I can go that way if it's the only way possible.

The answer is


You can do this with a single statement - assuming you are calling it from a JDBC-like connector with in/out parameters functionality:

insert into batch(batchid, batchname) 
values (batch_seq.nextval, 'new batch')
returning batchid into :l_batchid;

or, as a pl-sql script:

variable l_batchid number;

insert into batch(batchid, batchname) 
values (batch_seq.nextval, 'new batch')
returning batchid into :l_batchid;

select :l_batchid from dual;

There are no auto incrementing features in Oracle for a column. You need to create a SEQUENCE object. You can use the sequence like:

insert into table(batch_id, ...) values(my_sequence.nextval, ...)

...to return the next number. To find out the last created sequence nr (in your session), you would use:

my_sequence.currval

This site has several complete examples on how to use sequences.


You can use the below statement to get the inserted Id to a variable-like thing.

INSERT INTO  YOUR_TABLE(ID) VALUES ('10') returning ID into :Inserted_Value;

Now you can retrieve the value using the below statement

SELECT :Inserted_Value FROM DUAL;

Doing it as a stored procedure does have lot of advantages. You can get the sequence that is inserted into the table using syntax insert into table_name values returning.

Like:

declare
some_seq_val  number;
lv_seq        number;
begin
some_seq_val := your_seq.nextval;
insert into your_tab (col1, col2, col3) 
values (some_seq_val, val2, val3) returning some_seq_val into lv_seq;

dbms_output.put_line('The inserted sequence is: '||to_char(lv_seq));
end;
/

Or just return some_seq_val. In case you are not making use of SEQUENCE, and arriving the sequence on some calculation, you can make use of returning into effectively.


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to stored-procedures

How to create temp table using Create statement in SQL Server? How do I pass a list as a parameter in a stored procedure? SQL Server IF EXISTS THEN 1 ELSE 2 Stored procedure with default parameters Could not find server 'server name' in sys.servers. SQL Server 2014 How to kill all active and inactive oracle sessions for user EXEC sp_executesql with multiple parameters MySQL stored procedure return value SQL Server: use CASE with LIKE SQL server stored procedure return a table

Examples related to identity-column

BULK INSERT with identity (auto-increment) column Inserting into Oracle and retrieving the generated sequence ID Adding an identity to an existing column How do you determine what SQL Tables have an identity column programmatically

Examples related to scope-identity

Inserting into Oracle and retrieving the generated sequence ID