CODEWITHSUNDEEP
c#sqloracle-databaseoracle-manageddataaccess
LasseF
LasseF

Reputation: 1

Oracle Managed Data Access Returning Statement Causes ORA-01722: invalid number

Im trying to implement an insert statement using a returning statement to return the auto incremented id of the new row. Doing this causes an ORA-01722: invalid number exception even though I am pretty sure that i setup all the params correctly.

Im sure it is the return variable that is causing the problem since without it everything works just fine.

Here is the Oracle command text:

INSERT INTO ANGESTELLTER (
    NAME,
    VORNAME,
    GEHALT,
    KOMMISSION,
    CHEF)
VALUES (:NAME, :VORNAME, :GEHALT, :KOMMISSION, :CHEF)
RETURNING ID INTO :generatedID

And this is the way i setup the generatedID return parameter:

command.Parameters.Add("generatedID", OracleDbType.Decimal, ParameterDirection.ReturnValue);
command.Parameters["generatedID"].Size = 25; // Someone posted that this is needed for the query to work in their case

The Oracle Database type is simply Number and when I query the ID a decimal is returned in C#.

I tried running the command in SqlDeveloper where it actually worked I tried changing the database type to number and using decimal since before we were using Number(5,0) and Int32, but that also didnt work. I tried using ParameterDirections.Output but that did not change anything. I tried the command in SqlDeveloper where it did work with the data provided to the insert command. I also tried settings a default value for the parameter so that it isnt null.

Upvotes: 0

Views: 142

Answers (1)

LasseF
LasseF

Reputation: 1

Thanks to Paul W I now know that tracing is a thing which was actually very informative but did not reveal the error.

So I tested the command manually in my code, creating all parameters like this:

const string insertCommandReturning = """
                                      INSERT INTO ANGESTELLTER (NAME, VORNAME, GEHALT, KOMMISSION, CHEF)
                                      VALUES (:NAME, :VORNAME, :GEHALT, :KOMMISSION, :CHEF)
                                      RETURNING ID INTO :generatedID
                                      """;

var cmd = OracleDatabase.CreateCommand(insertCommandReturning);
cmd.BindByName = false;
cmd.Parameters.Add("NAME", OracleDbType.Varchar2).Value = "John";
cmd.Parameters.Add("VORNAME", OracleDbType.Varchar2).Value = "Doe";
cmd.Parameters.Add("GEHALT", OracleDbType.Double).Value = 50000;
cmd.Parameters.Add("KOMMISSION", OracleDbType.Double).Value = 0.1;
cmd.Parameters.Add("CHEF", OracleDbType.Int32).Value = null;
cmd.Parameters.Add("generatedID", OracleDbType.Decimal, ParameterDirection.Output);
cmd.ExecuteNonQuery();
decimal returnval = ((OracleDecimal)cmd.Parameters["generatedID"].Value).Value;

This worked flawlessly, but basically doesn't differ from my generated parameter assignment.

But then I remembered that I am not adding my parameters in this order since that is not the way my classes are reflected and the generatedID is added first. And the default parameter binding is not via name but via order, which I thought wasn't the case when using names for the parameters.

Therefore, this code doesn't work:

const string insertCommandReturning = """
                                      INSERT INTO ANGESTELLTER (NAME, VORNAME, GEHALT, KOMMISSION, CHEF)
                                      VALUES (:NAME, :VORNAME, :GEHALT, :KOMMISSION, :CHEF)
                                      RETURNING ID INTO :generatedID
                                      """;

var cmd = OracleDatabase.CreateCommand(insertCommandReturning);
cmd.BindByName = false;
cmd.Parameters.Add("generatedID", OracleDbType.Decimal, ParameterDirection.Output);
cmd.Parameters.Add("NAME", OracleDbType.Varchar2).Value = "John";
cmd.Parameters.Add("VORNAME", OracleDbType.Varchar2).Value = "Doe";
cmd.Parameters.Add("GEHALT", OracleDbType.Double).Value = 50000;
cmd.Parameters.Add("KOMMISSION", OracleDbType.Double).Value = 0.1;
cmd.Parameters.Add("CHEF", OracleDbType.Int32).Value = null;
cmd.ExecuteNonQuery();
decimal returnval = ((OracleDecimal)cmd.Parameters["generatedID"].Value).Value;

But this code does:

const string insertCommandReturning = """
                                      INSERT INTO ANGESTELLTER (NAME, VORNAME, GEHALT, KOMMISSION, CHEF)
                                      VALUES (:NAME, :VORNAME, :GEHALT, :KOMMISSION, :CHEF)
                                      RETURNING ID INTO :generatedID
                                      """;

var cmd = OracleDatabase.CreateCommand(insertCommandReturning);
cmd.BindByName = true;
cmd.Parameters.Add("generatedID", OracleDbType.Decimal, ParameterDirection.Output);
cmd.Parameters.Add("NAME", OracleDbType.Varchar2).Value = "John";
cmd.Parameters.Add("VORNAME", OracleDbType.Varchar2).Value = "Doe";
cmd.Parameters.Add("GEHALT", OracleDbType.Double).Value = 50000;
cmd.Parameters.Add("KOMMISSION", OracleDbType.Double).Value = 0.1;
cmd.Parameters.Add("CHEF", OracleDbType.Int32).Value = null;
cmd.ExecuteNonQuery();
decimal returnval = ((OracleDecimal)cmd.Parameters["generatedID"].Value).Value;

All I had to do was enable OracleCommand.BindByName

Upvotes: 0

Related Questions