Oracle "Ora-01008: Not All Variables Bound" Error W/ Parameters

PL/SQL : ORA-01008: not all variables bound

Substitution is performed by the client tool (SQL*Plus, SQL Developer etc) before sending the block to the database for processing, so you need to include a substitution variable explicitly.

Also, the &c in the generated expression is not a substitution variable but a bind variable (usually written as :c). I must admit I was surprised that the compiler treated it as one, because normally bind variables are prefixed with :, and I couldn't find anything in the documentation about it, but it seems to treat & the same as :.

Finally, bind variables in dynamic SQL are positional and not named, so &c in the dynamic string is unrelated to any substitution variable with the same name in the script, and :b would have the same meaning (and perhaps be less confusing).

declare
    sql_query varchar2(100);
    i  number;
begin
    select script into sql_query from formula_script where id = 1;

    execute immediate 'select '||sql_query||' from dual'
        into i
        using &input_number;

    dbms_output.put_line('Expression: '||sql_query);
    dbms_output.put_line('Result: '|| i);
end;
/

Enter value for input_number: 123
Expression: 1 - 1 * &c
Result: -122

Oracle ORA-01008: not all variables bound Error w/ Parameters

The ODP.Net provider from oracle uses bind by position as default. To change the behavior to bind by name. Set property BindByName to true. Than you can dismiss the double definition of parameters.

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

How to fix ORA-01008 : Not all variables bound

You should NOT specify the SQL query again. It's already specified. Change the line:

rs = stmt.executeQuery(sql); // method from java.sql.Statement

to:

rs = stmt.executeQuery(); // method from java.sql.PreparedStatement

The first method does not take parameters into consideration and runs the SQL "as is"... and therefore you get the error you mention.

C# ORA-01008: not all variables bound error

Now I can fixed this problem, I change paramater name to "?"

 string insertStatment = "INSERT INTO xxx.BTH_V_LOCATION (PLANTCODE, LOCATIONCODE, LOCATIONNAME, LOCATIONSTATUS, DEPARTMENTCODE, DEPARTMENTNAME) VALUES (?,?,?,?,?,?)";
        List<Foo> dataList = GetData();
        if (dataList.Count > 0)
        {
            using (OleDbConnection con2 = new OleDbConnection(connectionString))  //OleDbConnection
            {

                using (OleDbCommand cmd2 = new OleDbCommand(insertStatment, con2))  //OleDbCommand
                    {
                        con2.Open();
                        cmd2.Parameters.Clear();
                        foreach (var items in dataList)
                        {
                            cmd2.Parameters.Add("?", OleDbType.VarChar).Value = items.PLANTCODE;
                            cmd2.Parameters.Add("?", OleDbType.VarChar).Value = items.LOCATIONCODE;
                            cmd2.Parameters.Add("?", OleDbType.VarChar).Value = items.LOCATIONNAME;
                            cmd2.Parameters.Add("?", OleDbType.VarChar).Value = items.LOCATIONSTATUS;
                            cmd2.Parameters.Add("?", OleDbType.VarChar).Value = items.DEPARTMENTCODE;
                            cmd2.Parameters.Add("?", OleDbType.VarChar).Value = items.DEPARTMENTNAME;
                            //cmd2.ExecuteNonQuery();
                            //cmd2.Parameters.Clear();
                         }
                     cmd2.ExecuteNonQuery();
                     //cmd2.Parameters.Clear();
                    }
               } 
           }

Getting the error cx_Oracle.DatabaseError: ORA-01008: not all variables bound while trying to bind the value of a list in a merge statement

The problem is that you are binding by position, not by name. So you need an entry for every bind variable position. The value :2 in the first part of your SQL statement needs a value and the value :2 in the last part of your SQL statement also needs a value! (And so on for every bind variable mentioned in your SQL statement -- 20 of them). If you don't want to do that you can bind by name instead (and supply a dictionary of parameters).

One other possibility that may work is to perform a "query" of all of the parameters, as in this:

merge into bi.merchant_info_test target
using select :1 as x, :2, as y, :3 as z, ... from dual source
on target.atrans_id = source.atrans_id
when matched then update set merchant_info = source.merchant_info ...
when not matched then insert (ADDRESS, ...) values (source.address, ...)

That way you only have one place where the bind variables are required instead of two (for some of them).

The new Python driver released a couple of days ago (python-oracledb) has better error messages for this sort of thing when using thin mode (see the documentation for more details). It may be worth trying your example with that driver to find out the source of the issue.

On a side note, it is better to use executemany() when inserting multiple rows instead of iterating through the list and calling execute() repeatedly. The difference in performance can be striking, especially for large numbers of rows!

ORA-01008: not all variables bound. They are bound

I found how to run the query without error, but I hesitate to call it a "solution" without really understanding the underlying cause.

This more closely resembles the beginning of my actual query:

-- Comment
-- More comment
SELECT rf.flowrow, rf.stage, rf.process,
rf.instr instnum, rf.procedure_id, rtd_history.runtime, rtd_history.waittime
FROM
(
    -- Comment at beginning of subquery
    -- These two comment lines are the problem
    SELECT sub2.flowrow, sub2.stage, sub2.process, sub2.instr, sub2.pid
    FROM ( ...

The second set of comments above, at the beginning of the subquery, were the problem. When removed, the query executes. Other comments are fine.
This is not a matter of some rogue or missing newline causing the following line to be commented, because the following line is a SELECT. A missing select would yield a different error than "not all variables bound."

I asked around and found one co-worker who has run into this -- comments causing query failures -- several times.
Does anyone know how this can be the cause? It is my understanding that the very first thing a DBMS would do with comments is see if they contain hints, and if not, remove them during parsing. How can an ordinary comment containing no unusual characters (just letters and a period) cause an error? Bizarre.

---

Related Topics