5

Currently I have a table with this structure:

CREATE TABLE "DUMMY_SCHEMA"."NAMES"
(
  "ID" NUMBER(10,0) GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 CACHE 20) NOT NULL 
, "NAME" NVARCHAR2(1024) NOT NULL 
, CONSTRAINT "NAMES_PK" PRIMARY KEY ("ID")
);

In SQL Server I only need to do following to get the Id of the inserted row.

INSERT INTO [NAMES]([NAME])VALUES('Random'); SELECT SCOPE_IDENTITY() Id

What would be the equivalent for Oracle 12c?

wolφi
  • 8,091
  • 2
  • 35
  • 64
AceVentur4
  • 53
  • 1
  • 1
  • 3
  • I'd recommend to use table and column names without "QUOTES": `CREATE TABLE dummy_schema.names (id NUMBER....` is the usual way... – wolφi Jun 26 '18 at 20:48

1 Answers1

7

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).

APC
  • 144,005
  • 19
  • 170
  • 281
wolφi
  • 8,091
  • 2
  • 35
  • 64
  • Thanks. The variable :myvalue would be of what type? – AceVentur4 Jun 26 '18 at 22:16
  • Same datatype as your primary key column, in your case `NUMBER(10,0)`. What language are you using? – wolφi Jun 26 '18 at 22:18
  • C# with dapper as ORM – AceVentur4 Jun 26 '18 at 22:27
  • Sorry, don't know, but have a look at https://stackoverflow.com/questions/9789737/dapper-and-oracle-crud-issues-how-to – wolφi Jun 26 '18 at 22:30
  • thanks, finally made it work like you said! – AceVentur4 Jun 27 '18 at 22:58
  • insert into amalgam_care (id, name, activestatus, incorpcountry, sourcetable, sourcesystem) select fund_id, fund_name, active, domicile, 'EMIS_FUNDS', 'EMIS' from EMIS_FUNDS where status in (2, 3, 4) and active = 1 order by fund_id <- I have this query I want ID to be returned once inserted , how do i do this ? I have failed using above method @wolφi – Sithija Piyuman Thewa Hettige Feb 13 '23 at 06:57
  • @SithijaPiyumanThewaHettige: Returned to where? Please have a look at the link in the question "This question already has answers here" to check if one of the answers suits you. BTW, I wouldn't use "order by" in an insert. – wolφi Feb 14 '23 at 08:31