How do I return the value of an identity column (id) in Oracle 12c after insertion? Seems like most of the approaches out there uses sequence to get back the id of the inserted item.
Once we insert a row in a table, the @@IDENTITY function column gives the IDENTITY value generated by the statement. If we run any query that did not generate IDENTITY values, we get NULL value in the output. The SQL @@IDENTITY runs under the scope of the current session.
Use ALTER TABLE to add an IDENTITY column to an existing table. Note: To add an IDENTITY column to a table, the table must be at a top level. You cannot add an IDENTITY column as the column of a deeply embedded structured datatype.
You could use ORA_ROWSCN to get the last scn for each block on the table as an *approximate* means of seeing when things happened, or the flashback query syntax to get transaction information, but you are limited to the undo_retention settings on your database. Hope this helps.
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>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With