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.