0

I've been looking but still haven't found a solution that satisfies the problem. Is it possible to receive the ID of the last row inserted from a table in an Oracle database? I'm using C# and Dapper.Contrib, but the library uses the "select SCOPE_IDENTITY() id" strategy, which doesn't exist in Oracle.

I know that it is possible with triggers and or procedures, but I would like to know if there is a possibility of receiving this ID without triggers or procedures.

Flasher12
  • 13
  • 4
  • https://oracle-base.com/articles/misc/dml-returning-into-clause – Horaciux Jan 24 '23 at 18:25
  • Please check the accepted answer here: [Dapper and Oracle CRUD issues, how to?](https://stackoverflow.com/questions/9789737/dapper-and-oracle-crud-issues-how-to). You need to register an `out` parameter and use `returning` clause (regardless of the way the inserted value was generated: by trigger or by `insert` statement itself). – astentx Jan 25 '23 at 06:23

1 Answers1

1

I don't know C# nor Dapper, but this:

possibility of receiving this ID

sounds like returning into clause. Here's an example; as this is pure Oracle, I'm using PL/SQL (a procedure), although you said that you don't want it. Well, I don't know whether you can do that without ...

Sample table:

SQL> create table test
  2    (id      number generated always as identity,
  3     name    varchar2(20));

Table created.

Procedure; see returning into clause (line #7):

SQL> create or replace procedure p_insert (par_name in test.name%type)
  2  is
  3    l_id    test.id%type;
  4  begin
  5    insert into test (name)
  6      values (par_name)
  7      returning id into l_id;
  8
  9    dbms_output.put_line('ID = ' || l_id);
 10  end;
 11  /

Procedure created.

Testing:

SQL> set serveroutput on
SQL> exec p_insert('Little');
ID = 1                               --> that's ID, returned from the INSERT statement

PL/SQL procedure successfully completed.
    
SQL> select * from test;

        ID NAME
---------- --------------------
         1 Little

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57