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
3 Answers
38
Use the PL/SQL RETURNING clause:
insert into mytable (...) values (...)
returning id into v_id;
Tony Andrews
- 129,880
- 21
- 220
- 259
-
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
0
use the results.lastRowid to make a query on the same table like "SELECT * FROM YOUR_TABLE WHERE ROWID = :lastRowid"
lastRowid looks like "AAAS/tAAFAAAA/QAAB"
Jobin
- 79
- 3
- 8
0
You need to use a bind to recovery the value.
. . .
const result = await connection.execute(
`INSERT INTO mytable (mydata) VALUES ('Hello') RETURN myid INTO :id`,
{id : {type: oracledb.NUMBER, dir: oracledb.BIND_OUT } }
);
console.log(result.outBinds.id); // print the ID of the inserted row
Jonathan
- 1