I spent several hours with a strange behaviour in Oracle.ManagedDataAccess.Core in my C# web project in visual studio 2022. The version of that package was 3.21.61.
I wanted to insert data into a table and return the rowid for the inserted record for further use in my code. For that I wanted to use the "insert returning into" - clause in oracle sql.
I found quite a lot of examples explaining how to do this but it didn't work like it was supposed to; rowid returned always was blank (empty string). I tried all the solutions that other had proposed like use a pl/sql block around the insert statement and others proposed using ReturnValue instead of Output for the direction of the parameter.
At first I tried creating the parameter like below and it then returned just an empty string:
cmd.Parameters.Add("P_ROWID", OracleDbType.NVarchar2, ParameterDirection.Output);The thing that fooled me in the wrong directions while looking for the correct solution was that using this line didn't return an error at all! Getting an error would have helped a lot!
So the solution was to add two more parameters to the Parameters.Add-statement!
The working code that is returning a correct rowid:
// testClass
[TestClass()]
public class testReturningTests
{
private static testReturning tr = new testReturning();
[TestMethod()]
public void queryTest()
{
Console.WriteLine(tr.query());
}
}
// program
using System.Data;
using Oracle.ManagedDataAccess.Client;
namespace test.Data
{
public class testReturning
{
public string query()
{
OracleConnection con = new OracleConnection("User ID=TEST_USER;"+
"Password=testpassword; Data Source=(DESCRIPTION=(ADDRESS="+
"(PROTOCOL=tcp)(HOST=servername.mydomain.com)(PORT=1521))"+
"(CONNECT_DATA=(SERVICE_NAME=mydb)))");
con.Open();
string sql = "insert into countrycodes " +
" (COUNTRYCODE, COUNTRYNAME) " +
" values ( :P_COUNTRYCODE,: P_COUNTRYNAME) " +
" returning rowidtochar(rowid) into :P_ROWID";
OracleCommand cmd = new OracleCommand(sql, con);
cmd.BindByName = true;
cmd.Parameters.Add("P_COUNTRYCODE ", OracleDbType.NVarchar2, "SE", ParameterDirection.Input);
cmd.Parameters.Add("P_COUNTRYNAME ", OracleDbType.NVarchar2,"SWEDEN", ParameterDirection.Input);
cmd.Parameters.Add("P_ROWID", OracleDbType.NVarchar2, 64, null, ParameterDirection.Output);
cmd.ExecuteNonQuery();
return cmd.Parameters["P_ROWID"].Value.ToString();
}
}
}
