I have Oracle 11g (I think?) and VS2010.
I am using an insert statement with the returning clause to return an identity value that is created by a Sequence/Trigger. The problem I am having is that it is not updating the out parameter in C#. Here's some sample code. I have tested the insert statement in SQL Developer and it works. The C# statement does do the insert, but the out parameter remains null. I have tried ParameterDirection as ReturnValue, InputOutput, and Output. None of those worked. The output value remains null no matter what I try to do. I even lifted this example (Oracle Sequences), ran it, and it also errored and is not returning out parameters. (Error Msg: Unable to cast object of type 'Oracle.DataAccess.Types.OracleDecimal' to type 'System.IConvertible'.Couldn't store <null> in ID Column. Expected type is Int32.).
I have searched and searched the web over and I cannot figure out why this does not work. Any help would be most appreciated!!
MyTable
MyTable_ID Number (38)
Note Varchar2 (500 byte)
=================================================
try
{
if (insertCmd.Connection.State == ConnectionState.Closed)
insertCmd.Connection.Open();
int recs = insertCmd.ExecuteNonQuery();
this.Id = Convert.ToInt32(insertCmd.Parameters[":Id"].Value);
}
catch (Exception err) { }
finally { insertCmd.Connection.Close(); }
private OracleCommand insertCmd
{
get
{
OracleCommand insCmd = Context.DataBaseConnection.CreateCommand();
insCmd.CommandText = string.Format(
@"Insert into MyTable (Note)
Values (:Note)
Returning MyTable_ID into :Id");
insCmd.Parameters.Clear();
OracleParameter noteParam = new OracleParameter(":Note", OracleDbType.Varchar2, Note, ParameterDirection.Input);
OracleParameter idParam = new OracleParameter(":Id", OracleDbType.Int32, 38, "Calibration_Session_Id");
idParam.Direction = ParameterDirection.Output;
insCmd.Parameters.Add(idParam);
insCmd.Parameters.Add(noteParam);
insCmd.BindByName = true;
return insCmd;
}
}