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>
Insert and return value in oracle
The RETURNING INTO
clause is what you need:
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
l_id person.PersonId%type;
BEGIN
INSERT INTO person ( name ) VALUES ( 'Radouane' )
RETURNING id INTO l_id;
DBMS_OUTPUT.PUT_LINE( 'Returning ID value is : '||l_id );
END;
/
Replace "id" with your primary key column and "l_id" with the variable you want to put the value in.
There are plenty of examples available on the internet. Note that is is limited to PL/SQL, it cannot be used in SQL.
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 return newly inserted record column value
CREATE OR REPLACE PROCEDURE TST_SMS_IN
(
--P_MSISDN NUMBER,
P_MOBILE_NO varchar2,
P_SMS_BODY VARCHAR2,
p_IN_ID INTEGER
)
IS
V_SYSTEM_DATE DATE;
V_YEAR NUMBER;
V_YEAR_SYSTEM NUMBER;
V_TABLE_NAME VARCHAR2(100) :='';
V_SQL VARCHAR2(1000) :='';
V_ERROR_MESSAGE VARCHAR2(1000) :='';
P_AVG NUMBER;
V_CREATED_BY NUMBER;
BEGIN
INSERT INTO SMS_IN(IN_ID,MOBILE_NO,SMS_BODY,STATUS,USERS,REMARKS,IN_TIME) VALUES
(SMS_IN_SEQ.NEXTVAL,P_MOBILE_NO,P_SMS_BODY,0,'gp','TEST',sysdate);
p_IN_ID := SMS_IN_SEQ.CURRVAL;
COMMIT;
END TST_SMS_IN;
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).
Return ID in insert oracle
USE RETURNING INTO
in the insert with an OUT parameter. Use this parameter P_ESID
in your java code.
CREATE OR REPLACE PACKAGE BODY PLUSALT.INSERTAR
IS
PROCEDURE INSERTAR_ESTRATEGIA(
P_USUARIO IN VARCHAR2,
P_CAMPAÑA IN NUMBER,
P_FKPLANTILLA IN NUMBER,
P_NOMBRE IN VARCHAR2,
P_ESID OUT NUMBER)
IS
BEGIN
INSERT
INTO ESTRATEGIA VALUES
(
NULL,
P_CAMPAÑA,
P_USUARIO,
P_FKPLANTILLA,
P_NOMBRE,
NULL,
SYSDATE,
1,
'Y'
) RETURNING ESID INTO P_ESID ;
END;
END;
/
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
How to Create Foreign Keys Across Databases
Oracle/Sql: Wm_Concat & Order By
Why Can't I Exclude Dependent Columns from 'Group By' When I Aggregate by a Key
How to Pass a Variable That Contains a List to a Dynamic SQL Query
SQL Trigger After Insert Update Another Table with Conditions
Performance of Inner Join VS Cartesian Product
Delphi: Paradox Db Field Name Issue (Spaces in Field Name)
Count Number of Not Null Fields Within a Row
Why Oracle Is Saying Not a Group by Expression
Linq to SQL "Not Like" Operator
How to Prevent Ssis from Writing Column Names to the Flat File Output
Using Insert into with 'Select' to Supply Some Values But Not Others (Access 2010)
SQL Select for All Records That May Holds Specific Value
Calculate Difference Between Start_Time and End_Time in Seconds from Unix_Time Yyyy-Mm-Dd Hh:Mm:Ss