Oracle - Returning newly inserted row's primary key using "returning" keyword. Using a select statement instead of values and getting &q

This goes along with my last post. We were trying to use the returning statement to return the value of a newly inserted row when using an OracleDataAdapter and Parameter.Direction = ParameterDirection.ReturnValue. The difference is, we were using a select statement instead of values() as our input parameters.

Here is a simple example...

OracleDataAdapter oda = new OracleDataAdapter();
oda.InsertCommand = RemedyMD.CreateCommand();
oda.InsertCommand.CommandType = CommandType.Text;
oda.InsertCommand.CommandText =
"INSERT INTO users (name, email) (select name, email where firstName = :firstName) returning userID into :out";

oda.InsertCommand.Parameters.Add("firstName", OracleType.VarChar);
oda.InsertCommand.Parameters["firstName"].Value = firstName;

oda.InsertCommand.Parameters.Add("out", OracleType.Number);
oda.InsertCommand.Parameters["out"].Direction = ParameterDirection.ReturnValue;

oda.InsertCommand.ExecuteNonQuery();

When you try to execute this query, you will get an oracle error. ORA-00933: SQL command not properly ended. After researching this for a couple of hours trying to figure out how to get it to work, I found out that there is NO way to get it to work. Either you have to use Values() instead of your select statement, or perform a second statement to retrieve the newly created primary key value.

Oracle - Getting the primary key value of a newly inserted row using the "returning" keyword and an OracleDataAdapter

I learned how to use an OracleDataAdapter Parameter as an "out parameter" by setting it's direction = ParameterDirection.ReturnValue. Using this with an insert statement is a great way to return the primary key value of the newly inserted row.

Here is a simple example...

OracleDataAdapter oda = new OracleDataAdapter();
oda.InsertCommand = RemedyMD.CreateCommand();
oda.InsertCommand.CommandType = CommandType.Text;
oda.InsertCommand.CommandText =
                  "INSERT INTO users (name, email) VALUES (:name, :email) " +
                  "returning userID into :out";

oda.InsertCommand.Parameters.Add("name", OracleType.VarChar);
oda.InsertCommand.Parameters["name"].Value = userName;
oda.InsertCommand.Parameters.Add("email", OracleType.VarChar);
oda.InsertCommand.Parameters["email"].Value = userEmail;

oda.InsertCommand.Parameters.Add("out", OracleType.Number);
oda.InsertCommand.Parameters["out"].Direction = ParameterDirection.ReturnValue;

oda.InsertCommand.ExecuteNonQuery();

After executing the query, you can then pull the returned value out like this...

int  newUserId = -1;

if(int.TryParse(oda .InsertCommand.Parameters["out"].Value.ToString(), out newUserId)){

    //  Perform additional query that required the newly inserted row's primary key

}

It was fairly hard for me to find (google) this information, so I figured I'd post it in hopes that it would help someone else.