Does the extension method InsertGetId works for Oracle?

[frenkhub]

Hello, thanks for developing this project.

Does the extension method InsertGetId works for Oracle?

[ahmad-moussawi]

I don't think so, could you provide what is the correct way to fetch the last inserted id in Oracle so we can implement it

[frenkhub]

Sorry I'm not an expert of Oracle but at the moment when I call the method InsertGetId with an Oracle database it returns an exception "The sequence is empty", by the way the row is inserted. After looking the code it seems not implemented for the Oracle compiler. Why not returning a NotImplementedException istead in this case with a message?

I searched a bit now for a possible solution, I found https://oracle-base.com/articles/misc/dml-returning-into-clause. I tried it with the SqlDeveloper and seems working. The only problem seems that it is necessary tell the OracleCompiler what is the name of the autogenerated column (In my case "Id"). This is my try:

SET SERVEROUTPUT ON
DECLARE
last_generated_id "Team"."Id"%TYPE;
BEGIN
INSERT INTO "Team" ("Name", "Balance", "EnemyTeamId", "History") VALUES ('JUVENTUS', 123.34, null, 'a long history for this club')
RETURNING "Id" INTO last_generated_id;
COMMIT;

DBMS_OUTPUT.put_line(last_generated_id);
END;

[ahmad-moussawi]

Actually the Oracle support is a bit struggling, since we don't have a ready environment for Oracle, throwing NotImplementedException and mentioning that in the docs is the way to go in the short term.

I will keep this open, since we need more research to evaluate what is the best way for different Oracle versions.

[JordanMarr]

Hi Ahmad,

I am currently implementing Oracle support in my data library (which uses SqlKata) and today I confirmed that appending the following to my INSERT command does indeed work for Oracle:

var identityField = "REGION_ID";

// Append Oracle identity query + output parameter
cmd.CommandText = cmd.CommandText + $" returning \"{identityField}\" into :outputParam";
var outputParam = cmd.CreateParameter();
outputParam.ParameterName = "outputParam";
outputParam.DbType = System.Data.DbType.Decimal;
outputParam.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(outputParam);
await cmd.ExecuteNonQueryAsync();
var returnId = System.Convert.ChangeType(outputParam.Value, typeof(int));

Oracle table definition:

CREATE TABLE regions
  (
    region_id NUMBER(10,0) GENERATED BY DEFAULT AS IDENTITY
    START WITH 5 PRIMARY KEY,
    region_name VARCHAR2(50) NOT NULL
  );

It's a little different from SQL Server in that you must use an output parameter + ExecuteNonQueryAsync instead of using ExecuteScalarAsync as you do in SQL Server. Also it does require the name of the identity column.

[JordanMarr]

Also, if you are using Docker for your test databases, I would be happy to contribute the custom Oracle docker image that I am in the process of tweaking now for Oracle development in my project.