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.
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
ORA-01008: not all variables bound error and can't identify an unbound variable
According to Oracle's docs:
If the dynamic SQL statement does not represent an anonymous PL/SQL block or a CALL statement, repetition of placeholder names is insignificant. Placeholders are associated with bind arguments in the USING clause by position, not by name.
Which means, in this case, the parameter binding is made by position, not by name.
So you need to provide as many parameters in the USING clause as there are parameters strings in the SQL query, and in the same order.
open cur for v_sql1 using C_TOTAL_ROW_COUNT, C_TOTAL_ROW_COUNT, TOTAL_ROW_COUNT;
Also, make sure all the parameters have value.
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;
...
}
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!
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.
Related Topics
Unity3D Ui, Calculation for Position Dragging an Item
Adding Custom Properties for Each Request in Application Insights Metrics
Why Do I Get "System.Data.Datarowview" Instead of Real Values in My Winforms Listbox
Get Application's Window Handles
How to Serialize and Save a Gameobject in Unity
With Rx, How to Ignore All-Except-The-Latest Value When My Subscribe Method Is Running
Does Filestreamresult Close Stream
Advantage of Using Thread.Start VS Queueuserworkitem
Simulating Keyboard with Sendinput API in Directinput Applications
ASP.NET MVC Routing - Add .HTML Extension to Routes
Why Does the Linq Cast<> Helper Not Work with the Implicit Cast Operator
Making Specific Text Boldefaced in a Textbox
How to Convert a String to Rtf in C#
Outofmemoryexception When I Read 500Mb Filestream
How to Figure Out Which Key of Modelstate Has Error