"Index was outside the bounds of the array" error while trying to retrieve return value from an insert statement

Question

I am trying to insert into a table while returning its identity value. But "Index was outside the bounds of the array" error is thrown. I can execute query in dbForge successfully but not when I try to execute query in C# with oracle managed data access . Query is very simple . If I disable transaction, the row is inserted in the database but i get the error and cannot get the return value.

    var query = @"insert into table1  VALUES (97,'Mondon') RETURNING Id INTO :id";
                    OracleTransaction transaction = null;
                    using (var connection = new OracleConnection(_conStr))
                    {
                        try
                        {
                            connection.Open();
                            var command = connection.CreateCommand();
                            transaction = connection.BeginTransaction();
                            command.Transaction = transaction;
                            command.CommandText = query;
                            command.CommandTimeout = 5 * 60;
    
                            command.Parameters.Add(new OracleParameter("id", OracleDbType.Int32, ParameterDirection.ReturnValue));
    
                            var result = command.ExecuteNonQuery();
                            transaction.Commit();
                            var id = Convert.ToInt32(command.Parameters["id"].Value);
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            Logger.LogError(ex);
}
}

Answer 1

1. you have too much code
2. you have misconception(s)

Let me know if you have questions, see comments inline

int newId = 0;
// NOTE, if you don't insert into field 'ID' you need to list fields
var sql = "insert into table1 (fld1, fd2) VALUES (97,'Mondon') RETURNING Id INTO :id";
                        
try
{
    using (var conn = new OracleConnection(_conStr))
    {
        using (var cmd = new OracleCommand(sql, conn))
        {                   
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new OracleParameter("id", OracleDbType.Int32, ParameterDirection.Output)); // this is output, not return
        
            conn.Open();          
            var count = cmd.ExecuteNonQuery();
          
            if (count > 0) // table can have a trigger so number of rows changed can be more than 1
            {
                // YOUR BIG MISCONCEPTION HERE (FIXED)
                OracleDecimal val = (OracleDecimal)cmd.Parameters["id"].Value; // this returns special oracle struct
                int newId = val.ToInt32();   // you can use val.IsNull but here it is not possible            
            }
            else 
                throw new Exception("Value not inserted");

        }
    }
}
catch (Exception ex)
{
    Logger.LogError(ex);
}

Note that for insert of a single record explicit transaction is not needed

Answer 2

Old post, but I had this same problem today, and found a solution here: https://stackoverflow.com/a/29660204/210916

"You need to declare the variable as shown below. As a rule of thumb, always test your query on the Oracle server before you embed it into your code. Most, importantly use parametrized Store Procedures to avoid sql injection attacks. So Do not embed queries into your code." @Dan Hunex

In your query, you need to declare id before INSERT command

Answer 3

I think you have to encapsulate the insert into a function:

create function InsertTable1(n in integer, val in varchar2) return integer as
   res integer;
begin
   insert into table1  VALUES (n, val) RETURNING Id INTO res;
   RETURN res;
end;

And then in your application:

var query = @"BEGIN :0 := InsertTable1(97,'Mondon'); END;";

It would be a good idea to define also the input values as bind-parameters, rather than static strings.

A full dynamic solution could be similar to this:

create function InsertTable(cmd in varchar2) return integer as
   res integer;
begin
   EXECUTE IMMEDIATE cmd USING OUT res;
   RETURN res;
end;


var query = @"BEGIN :0 := InsertTable('insert into table1  VALUES (97,''Mondon'') RETURNING Id INTO :res'); END;";