1

We are trying out the Devart Oracle adapter but are having problems with existing code that worked with the Oracle's DataAccess DLL. We get the following example error on command.ExecuteNonQuery():

ORA-01400: cannot insert NULL into ("DatabaseName"."table_name"."col3")

Here is some example code:

const string query = @"INSERT INTO table_name (table_name_id, col1, col2, col3)
    VALUES(table_name_id_seq.nextval, :col1, :col2, :col3)
    RETURNING table_name_id INTO :output_id";
OracleParameter outputParam = new OracleParameter(":output_id", OracleDbType.Long, ParameterDirection.Output);
OracleParameter[] parameters = new OracleParameter[]
{
    outputParam,
    new OracleParameter(":col1", OracleDbType.VarChar, col1, ParameterDirection.Input),
    new OracleParameter(":col2", OracleDbType.VarChar, col2, ParameterDirection.Input),
    new OracleParameter(":col3", OracleDbType.Long, col3, ParameterDirection.Input)
}

using (OracleCommand command = connection.CreateCommand())
{
    command.CommandText = query;
    command.CommandType = CommandType.Text;
    command.Parameters.AddRange(parameters);
    command.ExecuteNonQuery();
    int outputId = Convert.ToInt32(outputParam.Value.ToString());
}

What are we doing wrong? We are trying to insert a row using a sequence for the PK, and return the PK for that row all in one query.

Also, the query runs fine if I remove the output parameter and the returning line in the query.

xbrady
  • 1,683
  • 14
  • 23
  • Two possibilities occur to me. Using named parameters sometimes doesn't work as expected, and you have to give them in the right order - so moving the output parameter to the end of the array might make a difference. But maybe more likely, from [this](http://www.devart.com/dotconnect/oracle/articles/parameters.html) it looks like maybe you shouldn't have the colon in the first argument to `OracleParameter`? Not something I've used though so guessing really... – Alex Poole Feb 11 '13 at 22:22
  • @AlexPoole Thanks for the ideas. I tried adding the parameters in order and received the same error. I thought that order didn't matter when binding by name? I also tried removing the colon when creating the parameter and still received the error. The interesting thing is that we have hundreds of other queries that have the parameters created in this same way and they all seem to work. It is just this one query that has the RETURNING keyword in it. – xbrady Feb 12 '13 at 00:05
  • Order shouldn't matter, but I've seen it not work (can't find a link right now). Not the issue here anyway. Sorry I couldn't help! – Alex Poole Feb 12 '13 at 10:12

1 Answers1

0

Thank you for your report. We have reproduced the issue and are investigating it. As a workaround, please switch to the Direct Mode: http://www.devart.com/dotconnect/oracle/docs/?directmode.html .

Devart
  • 119,203
  • 23
  • 166
  • 186
  • Thanks, we unfortunately can't use direct mode on our network. As a workaround we just changed it into two queries. One to select the sequence nextval and one to do the insert. This will work fine until you guys are able to fix the problem. – xbrady Feb 12 '13 at 16:31
  • @xbrady Please send the DDL script of your table to our support address: http://www.devart.com/company/contact.html – Devart Feb 19 '13 at 16:29
  • @xbrady There is some weirdness: you have specified two parameters of OracleDbType.Long type (for table_name_id and col3 columns) but a table in Oracle can have only one LONG column. – Devart Feb 19 '13 at 16:36
  • 1
    We ended up using direct mode. We had to do some modifications to our code, but it was totally worth it. Thanks. – xbrady Nov 08 '13 at 20:32