OracleCommand type Text in C# isn't accepting parameters inside a subquery

Question

This piece of code doesn't work:

using (var conn = new OracleConnection(ConnString))
{
    conn.Open();
    using (var t = conn.BeginTransaction())
    {
        var sanitizer = new HtmlSanitizer();

        var sqlWithSubquery = @"INSERT INTO owner.dummy_table (idDog, idCat, idBunny) VALUES (
            SequenceDogs.nextval, 
            :Parameter_IdCat, 
            (SELECT idBunny FROM owner.bunny_table WHERE
            IdTree = :Parameter_IdTree))";

        var cmdInsertWithSubquery = new OracleCommand(sqlWithSubquery.ToString(), conn);
        cmdInsertWithSubquery.Parameters.Add(new OracleParameter("Parameter_IdCat", OracleDbType.Int32)).Value = sanitizer.Sanitize(idCat);
        cmdInsertWithSubquery.Parameters.Add(new OracleParameter("Parameter_IdTree", OracleDbType.Int32)).Value = sanitizer.Sanitize(idTree);
        cmdInsertWithSubquery.CommandType = System.Data.CommandType.Text;

        var result = cmdInsertWithSubquery.ExecuteNonQueryAsync();
        if (result.Status == TaskStatus.Faulted)
        {
            sbLog.AppendLine(result.Exception.InnerException.Message.ToString());
        }
        else
        {
            sbLog.AppendLine("Success");
        }

        t.Commit();

        conn.Close();
    }
}

It yealds to error: ORA-01400: cannot insert NULL into ("owner"."dummy_table"."idBunny")

While the following works just fine:

using (var conn = new OracleConnection(ConnString))
{
    conn.Open();
    using (var t = conn.BeginTransaction())
    {
        var sanitizer = new HtmlSanitizer();

        // Doing the subquery apart, because for some reason it won't work inside an INSERT
        var sqlSubquery = @"SELECT idBunny FROM owner.bunny_table WHERE
            IdTree = :Parameter_IdTree";
        var cmdSubquery = new OracleCommand(sqlSubquery.ToString(), conn);
        cmdSubquery.Parameters.Add(new OracleParameter("Parameter_IdTree", OracleDbType.Int32)).Value = sanitizer.Sanitize(idTree);
        cmdSubquery.CommandType = System.Data.CommandType.Text;
        var idBunny = cmdSubquery.ExecuteScalar();



        var sqlInsert = @"INSERT INTO owner.dummy_table (idDog, idCat, idBunny) VALUES (
            SequenceDogs.nextval, 
            :Parameter_IdCat, 
            :Parameter_IdBunny)";
        var cmdInsert = new OracleCommand(sqlInsert.ToString(), conn);
        cmdInsert.Parameters.Add(new OracleParameter("Parameter_IdCat", OracleDbType.Int32)).Value = sanitizer.Sanitize(idCat);
        cmdInsert.Parameters.Add(new OracleParameter("Parameter_IdBunny", OracleDbType.Int32)).Value = sanitizer.Sanitize(idBunny.ToString());
        cmdInsert.CommandType = System.Data.CommandType.Text;

        var result = cmdInsert.ExecuteNonQueryAsync();
        if (result.Status == TaskStatus.Faulted)
        {
            sbLog.AppendLine(result.Exception.InnerException.Message.ToString());
        }
        else
        {
            sbLog.AppendLine("Success");
        }

        t.Commit();

        conn.Close();
    }
}

The problem is solved, but it looks like an ugly solution, and I would like to know why the first approach is not valid.

Is it some bug on the binding process or is a feature by design?

`:Parameter_IdBunny` is equal to `SELECT idBunny FROM owner.bunny_table WHERE IdTree = :Parameter_IdTree` if and only if `owner.bunny_table` contains a single row with `IdTree` equal to the passed value (including both datatypes or if implicit type cast results into the same value). Is it your case? — astentx, Apr 11 '23 at 20:31
Both codes were tested with the same parameters and against the same database. First one worked, the second don't. — André Baptista, Apr 13 '23 at 11:50
My mistake above, the first one isn't working (with subquery), the second works. — André Baptista, Apr 13 '23 at 12:00
Could you fix this weird code indentation? It's difficult to read. — yacc, Apr 13 '23 at 12:11
Could you add information about the table layout? And why did you choose `SequenceDogs.nextval` instead of just NULL and let it auto-increment? — yacc, Apr 13 '23 at 12:33

yacc · Answer 1 · 2023-04-12T03:33:55.500

0

As far as I could gather from this post ( Oracle identity column and insert into select ) a work-around could be

INSERT INTO owner.dummy_table (idDog, idCat, idBunny) 
 SELECT * FROM (
   SELECT SequenceDogs.nextval, :Parameter_IdCat, bt.idBunny
     FROM owner.bunny_table bt
    WHERE bt.IdTree = :Parameter_IdTree);

It's untested but will perhaps get you towards a solution. And as already pointed out, make sure that NULL values cannot result for idBunny (add a idBunny IS NOT NULL to the WHERE clause to be safe).

edited Apr 12 '23 at 03:33

answered Apr 12 '23 at 02:47

yacc

2,915
4
19
33

The goal of the question is to understand why the first approach isn't working, not find one other approach that works (the question itself shows one work around) – André Baptista Apr 13 '23 at 11:52
You were talking of an *ugly* solution, so I wanted to help out with a more elegant fix. Did it work? I think your code didn't work due to the way Oracle handles sequences in intermediate results. You'll find some explanations if you follow the link I posted. – yacc Apr 13 '23 at 12:09

OracleCommand type Text in C# isn't accepting parameters inside a subquery

1 Answers1