Getting Auto-Generated IDs Back from Oracle in .NET: The RETURNING INTO Trap

If you've ever inserted a row into Oracle from .NET and needed the auto-generated ID back immediately — say, to use in a follow-up insert — you've probably reached for RETURNING INTO. And you've probably also hit a confusing type mismatch or invalid cast error trying to read the value back out. Here's why, and the fix that actually works.

The symptom

You write an insert like this:

var cmd = connection.CreateCommand();
cmd.CommandText = "INSERT INTO MY_TABLE (NAME) VALUES (:p_name) RETURNING ID INTO :p_id";
cmd.Parameters.Add(new OracleParameter("p_name", name));

var idParam = new OracleParameter("p_id", OracleDbType.Int32, ParameterDirection.Output);
cmd.Parameters.Add(idParam);

cmd.ExecuteNonQuery();

int newId = (int)idParam.Value; // <-- blows up here

It compiles fine. It runs without SQL errors. And then the cast to int throws — or worse, silently gives you a value that doesn't behave the way you expect.

The wrong assumption

The natural assumption is that an Oracle NUMBER column maps cleanly to a .NET int or decimal, the same way it would with most other databases — declare the output parameter type, cast the result, done.

Oracle's NUMBER type doesn't map that simply through OracleDbType.Int32.

The actual cause

Oracle's NUMBER type is really a variable-precision decimal, not a fixed-width integer. When you bind an output parameter as OracleDbType.Int32, you're asking ODP.NET to coerce Oracle's native numeric representation into a shape it doesn't naturally fit — which is where the cast failures and unexpected values creep in, especially once IDs get large or the underlying sequence has any quirks.

The fix

Bind the output parameter as OracleDbType.Decimal, and cast through OracleDecimal instead of straight to int:

var cmd = connection.CreateCommand();
cmd.CommandText = "INSERT INTO MY_TABLE (NAME) VALUES (:p_name) RETURNING ID INTO :p_id";
cmd.Parameters.Add(new OracleParameter("p_name", name));

var idParam = new OracleParameter("p_id", OracleDbType.Decimal, ParameterDirection.Output);
cmd.Parameters.Add(idParam);

cmd.ExecuteNonQuery();

OracleDecimal oracleId = (OracleDecimal)idParam.Value;
int newId = oracleId.ToInt32(); // clean, safe conversion

Going through OracleDecimal respects Oracle's actual numeric type instead of fighting it, and gives you a safe, explicit path to whatever .NET type you actually need afterward.

The broader lesson

Any time you're pulling a value back out of Oracle via RETURNING INTO, default to OracleDbType.Decimal for numeric columns and cast through OracleDecimal, rather than assuming a direct match to Int32 or Int64. It's a small pattern, but it's the difference between a clean insert-and-return flow and a cryptic runtime cast exception that has nothing to do with your actual logic.