ORA-01008 : Not all variables bound with nulls

Question

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

score 6 · Accepted Answer · edited Nov 02 '12 at 05:05

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;
        }
    }
}

Brilliant. Worked for me too! – Dev.Jaap Nov 10 '15 at 11:19 — Dev.Jaap, Nov 10 '15 at 11:19

score 1 · Answer 2 · edited May 23 '17 at 12:32

1

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;
    ...
}

edited May 23 '17 at 12:32

Community

1
1

answered Oct 31 '12 at 19:32

Jared Harley

8,219
4
39
48

Thanks, but it's actually declared at the top, and added at the bottom, in order/position, so `BindByName` isn't the issue I don't think. Curiously, I seem to be missing that property though... – Grant H. Oct 31 '12 at 19:33

codingbiz · Answer 3 · 2012-11-01T10:53:58.253

0

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

edited Nov 01 '12 at 10:53

answered Nov 01 '12 at 00:22

codingbiz

26,179
8
59
96

ORA-01008 : Not all variables bound with nulls

3 Answers3