CODEWITHSUNDEEP
c#oracle-database
Grant H.
Grant H.

Reputation: 3717

ORA-01008 : Not all variables bound with nulls

I'm creating a query and I cannot seem to get beyond the Not all variables bound error in Oracle

As best as I can tell, everything looks correct. What am I missing?

Code obfuscated a bit to protect the innocent...

OracleCommand execCmd = new OracleCommand();

OracleParameter outParam =
        new OracleParameter("ID", OracleType.Int32);
outParam.Value = DBNull.Value;
outParam.Direction = System.Data.ParameterDirection.Output;
execCmd.CommandText = "insert into o " +
        "(A, B, " +
        "C, D, E, " +
        "F, G, H, I, " +
        "J, K) " +
        "VALUES (:A, :B, :C, :D, :E, " +
        ":F, :G, :H, :I, :J, :K) " +
        "RETURNING O_ID INTO :ID";
execCmd.Parameters.AddWithValue("A", og.N);
execCmd.Parameters.AddWithValue("B", DBNull.Value);
execCmd.Parameters.AddWithValue("C", DBNull.Value);
execCmd.Parameters.AddWithValue("D", og.A);
execCmd.Parameters.AddWithValue("E",
    og.A1 + " " + og.A2 + " " + og.A3 +
    " " + og.C);
execCmd.Parameters.AddWithValue("F", DBNull.Value);
execCmd.Parameters.AddWithValue("G", DBNull.Value);
execCmd.Parameters.AddWithValue("H", og.Cs);
execCmd.Parameters.AddWithValue("I", ss);
execCmd.Parameters.AddWithValue("J", DBNull.Value);
execCmd.Parameters.AddWithValue("K", "N");
execCmd.Parameters.Add(outParam);
conn.executeCommand(execCmd, trx);

Upvotes: 2

Views: 9549

Answers (3)

Grant H.
Grant H.

Reputation: 3717

I was able to solve this eventually. Turned out that one of my strings was in fact null, and there's a bug in this version of OracleCommand (which is marked as deprecated) that causes it to drop null parameters. The solution for me was to run a fixup function before executing.

Also, as others stated, the order DOES matter. So, that needs to be correct as well.

Here's what worked for me, calling it right before execution:

private void PopulateNullParameters(OracleCommand cmd)
{
    foreach (OracleParameter p in cmd.Parameters)
    {
        if (p.Value == null)
        {
            p.Value = DBNull.Value;
        }
    }
}

Upvotes: 6

codingbiz
codingbiz

Reputation: 26406

Try to precede your parameter name with : colon

e.g.

  execCmd.Parameters.AddWithValue(":NAME", org.Name);
  execCmd.Parameters.AddWithValue(":EDP", DBNull.Value);
  execCmd.Parameters.AddWithValue(":EDD", DBNull.Value);
  execCmd.Parameters.AddWithValue(":ACRONYM", org.Acronym);    
  ....

Upvotes: 0

Jared Harley
Jared Harley

Reputation: 8337

It looks like you're missing a parameter for ID, in this last line:

"RETURNING ORGANIZATION_ID INTO :ID";

Also, based on this SO question, it appears that the OracleCommand object binds the parameters by position, so if your parameters are out of order, you might want to look at using the BindByName property:

using(OracleCommand cmd = con.CreateCommand()) {
    ...
    cmd.BindByName = true;
    ...
}

Upvotes: 1

Related Questions