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.
This question is related to
sql
oracle
stored-procedures
identity-column
scope-identity
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.
Source: Stackoverflow.com