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?

Answer 1

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).