I want to retrieve the id of a newly inserted record with an auto incrementing id column (using the sequence and trigger method). What is the standard way to do this?
Asked
Active
Viewed 2.2k times
1 Answers
37
Use the PL/SQL RETURNING clause:
insert into mytable (...) values (...)
returning id into v_id;
Tony Andrews
- 126,319
- 20
- 218
- 255
-
1Does this work only in PL/SQL? I get a syntax error with regular SQL. – prauchfuss Dec 25 '14 at 00:20
-
1Yes it is PL/SQL only – Tony Andrews Dec 25 '14 at 11:09
-
Can it be used in `execute immediate`? – Akshay Dec 01 '15 at 10:40
-
@akshay Yes: `execute immediate 'insert into mytable (...) values (...) returning id into :retval' using out v_id;` – Tony Andrews Dec 01 '15 at 10:44
-
This doesn't work for insert using a select due to possibility of inserting more than 1 row. I am still looking for a way to grab the most recently inserted id. Perhaps I can get it from the sequence. – Skystrider Jun 22 '17 at 17:24