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

Question

I have come across an Oracle problem for which I have so far been unable to find the cause. The query below works in Oracle SQL developer, but when running in .NET it throws:

ORA-01008: not all variables bound

I've tried:

• Changing the Oracle data type for lot_priority (Varchar2 or int32).
• Changing the .NET data type for lot_priority (string or int).
• One bind variable name is used twice in the query. This is not a problem in my other queries that use the same bound variable in more than one location, but just to be sure I tried making the second instance its own variable with a different :name and binding it separately.
• Several different ways of binding the variables (see commented code; also others).
• Moving the bindByName() call around.
• Replacing each bound variable with a literal. I've had two separate variables cause the problem (:lot_pri and :lot_priprc). There were some minor changes I can't remember between the two. Changing to literals made the query work, but they do need to work with binding.

Query and code follow. Variable names have been changed to protect the innocent:

SELECT rf.myrow floworder, rf.stage, rf.prss,
rf.pin instnum, rf.prid, r_history.rt, r_history.wt
FROM
(
    SELECT sub2.myrow, sub2.stage, sub2.prss, sub2.pin, sub2.prid
    FROM (
        SELECT sub.myrow, sub.stage, sub.prss, sub.pin,
            sub.prid, MAX(sub.target_rn) OVER (ORDER BY sub.myrow) target_row
            ,sub.hflag
        FROM (
            WITH floc AS 
            (
                SELECT flow.prss, flow.seq_num
                FROM rpf@mydblink flow
                WHERE flow.parent_p = :lapp
                AND flow.prss IN (
                    SELECT r_priprc.prss
                    FROM r_priprc@mydblink r_priprc
                    WHERE priprc = :lot_priprc
                )
                AND rownum = 1
            )
            SELECT row_number() OVER (ORDER BY pp.seq_num, rpf.seq_num) myrow,
                rpf.stage, rpf.prss, rpf.pin,
                rpf.itype, hflag,
            CASE WHEN rpf.itype = 'SpecialValue'
                THEN rpf.instruction
                ELSE rpf.parent_p
            END prid,
            CASE WHEN rpf.prss = floc.prss
                AND rpf.seq_num = floc.seq_num
                THEN row_number() OVER (ORDER BY pp.seq_num, rpf.seq_num)
            END target_rn
            FROM floc, rpf@mydblink rpf
            LEFT OUTER JOIN r_priprc@mydblink pp
                ON (pp.prss = rpf.prss)
            WHERE pp.priprc = :lot_priprc
            ORDER BY pp.seq_num, rpf.seq_num
        ) sub
    ) sub2
    WHERE sub2.myrow >= sub2.target_row
    AND sub2.hflag = 'true'
) rf
LEFT OUTER JOIN r_history@mydblink r_history
ON (r_history.lt = :lt
    AND r_history.pri = :lot_pri
    AND r_history.stage = rf.stage
    AND r_history.curp = rf.prid
)
ORDER BY myrow

---

public void runMyQuery(string lot_priprc, string lapp, string lt, int lot_pri) {
Dictionary<int, foo> bar = new Dictionary<int, foo>();
using(var con = new OracleConnection(connStr)) {
    con.Open();

    using(var cmd = new OracleCommand(sql.rtd_get_flow_for_lot, con)) { // Query stored in sql.resx
        try {
            cmd.BindByName = true;
            cmd.Prepare();
            cmd.Parameters.Add(new OracleParameter("lapp", OracleDbType.Varchar2)).Value = lapp;
            cmd.Parameters.Add(new OracleParameter("lot_priprc", OracleDbType.Varchar2)).Value = lot_priprc;
            cmd.Parameters.Add(new OracleParameter("lt", OracleDbType.Varchar2)).Value = lt;
            // Also tried OracleDbType.Varchar2 below, and tried passing lot_pri as an integer
            cmd.Parameters.Add(new OracleParameter("lot_pri", OracleDbType.Int32)).Value = lot_pri.ToString();
            /*********** Also tried the following, more explicit code rather than the 4 lines above: **
            OracleParameter param_lapp
                = cmd.Parameters.Add(new OracleParameter("lapp", OracleDbType.Varchar2));
            OracleParameter param_priprc
                = cmd.Parameters.Add(new OracleParameter("lot_priprc", OracleDbType.Varchar2));
            OracleParameter param_lt
                = cmd.Parameters.Add(new OracleParameter("lt", OracleDbType.Varchar2));
            OracleParameter param_lot_pri
                = cmd.Parameters.Add(new OracleParameter("lot_pri", OracleDbType.Varchar2));
            param_lapp.Value = lastProcedureStackProcedureId;
            param_priprc.Value = lotPrimaryProcedure;
            param_lt.Value = lotType;
            param_lot_pri.Value = lotPriority.ToString();
            //***************************************************************/
            var reader = cmd.ExecuteReader();
            while(reader.Read()) {
                // Get values from table (Never reached)
            }
        }
        catch(OracleException e) {
            //     ORA-01008: not all variables bound
        }
    }
}

---

Why is Oracle claiming that not all variables are bound?

Solution 1

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.

Solution 2

I know this is an old question, but it hasn't been correctly addressed, so I'm answering it for others who may run into this problem.

By default Oracle's ODP.net binds variables by position, and treats each position as a new variable.

Treating each copy as a different variable and setting it's value multiple times is a workaround and a pain, as furman87 mentioned, and could lead to bugs, if you are trying to rewrite the query and move things around.

The correct way is to set the BindByName property of OracleCommand to true as below:

var cmd = new OracleCommand(cmdtxt, conn);
cmd.BindByName = true;

You could also create a new class to encapsulate OracleCommand setting the BindByName to true on instantiation, so you don't have to set the value each time. This is discussed in this post

Solution 3

You have two references to the :lot_priprc binding variable -- while it should require you to only set the variable's value once and bind it in both places, I've had problems where this didn't work and had to treat each copy as a different variable. A pain, but it worked.

Solution 4

The solution in my situation was similar answer to Charles Burns; and the problem was related to SQL code comments.

I was building (or updating, rather) an already-functioning SSRS report with Oracle datasource. I added some more parameters to the report, tested it in Visual Studio, it works great, so I deployed it to the report server, and then when the report is executed the report on the server I got the error message:

"ORA-01008: not all variables bound"

I tried quite a few different things (TNSNames.ora file installed on the server, Removed single line comments, Validate dataset query mapping). What it came down to was I had to remove a comment block directly after the WHERE keyword. The error message was resolved after moving the comment block after the WHERE CLAUSE conditions. I have other comments in the code also. It was just the one after the WHERE keyword causing the error.

SQL with error: "ORA-01008: not all variables bound"...

WHERE
/*
    OHH.SHIP_DATE BETWEEN TO_DATE('10/1/2018', 'MM/DD/YYYY') AND TO_DATE('10/31/2018', 'MM/DD/YYYY')
    AND OHH.STATUS_CODE<>'DL'
    AND OHH.BILL_COMP_CODE=100
    AND OHH.MASTER_ORDER_NBR IS NULL
*/

    OHH.SHIP_DATE BETWEEN :paramStartDate AND :paramEndDate
    AND OHH.STATUS_CODE<>'DL'
    AND OHH.BILL_COMP_CODE IN (:paramCompany)
    AND LOAD.DEPART_FROM_WHSE_CODE IN (:paramWarehouse) 
    AND OHH.MASTER_ORDER_NBR IS NULL
    AND LOAD.CLASS_CODE IN (:paramClassCode) 
    AND CUST.CUST_CODE || '-' || CUST.CUST_SHIPTO_CODE IN (:paramShipto) 

SQL executes successfully on the report server...

WHERE
    OHH.SHIP_DATE BETWEEN :paramStartDate AND :paramEndDate
    AND OHH.STATUS_CODE<>'DL'
    AND OHH.BILL_COMP_CODE IN (:paramCompany)
    AND LOAD.DEPART_FROM_WHSE_CODE IN (:paramWarehouse) 
    AND OHH.MASTER_ORDER_NBR IS NULL
    AND LOAD.CLASS_CODE IN (:paramClassCode) 
    AND CUST.CUST_CODE || '-' || CUST.CUST_SHIPTO_CODE IN (:paramShipto)   
/*
    OHH.SHIP_DATE BETWEEN TO_DATE('10/1/2018', 'MM/DD/YYYY') AND TO_DATE('10/31/2018', 'MM/DD/YYYY')
    AND OHH.STATUS_CODE<>'DL'
    AND OHH.BILL_COMP_CODE=100
    AND OHH.MASTER_ORDER_NBR IS NULL
*/

Here is what the dataset parameter mapping screen looks like.

Solution 5

On Charles' comment problem: to make things worse, let

:p1 = 'TRIALDEV'

via a Command Parameter, then execute

select T.table_name as NAME, COALESCE(C.comments, '===') as DESCRIPTION
from all_all_tables T
Inner Join all_tab_comments C on T.owner = C.owner and T.table_name = C.table_name
where Upper(T.owner)=:p1
order by T.table_name

558 line(s) affected. Processing time: 00:00:00.6535711

and when changing the literal string from === to ---

select T.table_name as NAME, COALESCE(C.comments, '---') as DESCRIPTION
[...from...same-as-above...]

ORA-01008: not all variables bound

Both statements execute fine in SQL Developer. The shortened code:

            Using con = New OracleConnection(cs)
                con.Open()
                Using cmd = con.CreateCommand()
                    cmd.CommandText = cmdText
                    cmd.Parameters.Add(pn, OracleDbType.NVarchar2, 250).Value = p
                    Dim tbl = New DataTable
                    Dim da = New OracleDataAdapter(cmd)
                    da.Fill(tbl)
                    Return tbl
                End Using
            End Using

using Oracle.ManagedDataAccess.dll Version 4.121.2.0 with the default settings in VS2015 on the .Net 4.61 platform.

So somewhere in the call chain, there might be a parser that is a bit too aggressively looking for one-line-comments started by -- in the commandText. But even if this would be true, the error message "not all variables bound" is at least misleading.

View more solutions

Comments

[Charles Burns]

Very observant, and that is often an issue with Oracle when using their insane default of binding by position in query text. Unfortunately I've already tried using two variables: :lot_priprc and :lot_priprc2, and binding them separately.

[furman87]

Too bad it wasn't that easy, I'll be interested to know what the solution is and I'll keep thinking about it. Sounds like you've tried most everything at this point. I'm not sure if you had referenced that in your original post -- I don't always read the whole thing :)

[tsells]

Have you searched the Oracle Support Site for this? I have a feeling this is related to a binding bug. Which version of 11 are you using (including the patch version)?

[Charles Burns]

I'm using the 32-bit ODAC 11.2 Release 3 (11.2.0.2.1) in .NET 4 on VS2010 SP1. The server itself is as follows: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production PL/SQL Release 9.2.0.4.0 - Production TNS for Solaris: Version 9.2.0.4.0 - Production NLSRTL Version 9.2.0.4.0 - Production The coworker who's had this issue does not use .NET in any way (he uses Oracle only in Toad). I haven't searched the Oracle support site. I'll have to do that once this project nears completion. This is a fairly typical of an Oracle experience, from what I've seen.

[tsells]

This is a bug in R9. We had the same issue with our applications when the newer versions of oracle (odp) came out and we had to drop support for it. Oracle would not acknowledge this as an official bug. They told us we had to "change" the queries.

[grinch]

I was having the same problem and this worked for me. My comment was within the select clause of the outer most query. Thanks very much for posting.

[David R.]

This was the problem for me. Using .NET with the most up to date ODP and still it required that, but all is well now. Really a pain because I was using a Case in Insert and I'd hate to insert a different variable than the one being evaluated.

[Charles Burns]

BindByName was already set to true in all cases where I ran into this Oracle flaw. In other than the most trivial queries, Oracle's defaults make it nearly unusable, requiring that code is updated every time the query is changed.

[Vijay Jagdale]

Wow, that's weird, as this works fine for me. Do you have the latest ODP client drivers installed? If so, you may want to open a ticket with Oracle and report the bug based on your specific scenario and and your setup.

[Charles Burns]

The problem doesn't occur every time for us either. In one case, we had a query that worked fine for years and then suddenly had this problem, with no software or database changes made. Must have hit some sort of threshold set in the optimizer. As for Oracle's support, it would be easier to patch the bug in-memory using Itanium assembly language than deal with them. :)

[Curmudgeon]

+1 to your solution @VijayJagdale -- I hadn't run into this information anywhere else and it solved the severe, confusing problems I was having, even if it wasn't the same as OP's. Cheers ^_^

[Vijay Jagdale]

Thanks Curmudgeon. And you're right, this is very confusing and severe. When Oracle gives tries to bind variables in different order it gives weird errors (like ORA1722-invalid number), or worse, it returns bad select results, or inserts/updates/deletes with wrong values, and that can be very hard to figure out, if you didn't know that bound variables are out of sync.

[Charles Burns]

Friends don't let friends use Oracle.

[Charles Burns]

This is a bug in Oracle 10, Oracle 11, and Oracle 12 as well. Sometimes the problem manifests as a query that returns no rows for no apparent reason rather than "not all variables bound." I wonder what kind of quality standards allow such an extremely severe bug to continue without a fix for more than ten years.

[Charles Burns]

We've seen the issue in Java applications, too.

[crowne]

-- is probably being interpreted as a comment line by the SQL parser

[Frédéric]

With Oracle.ManagedDataAccess 12.1.2400 on NuGet (file product version 4.121.2.20150926), we were not having that bug, but with newer versions up to current 12.2.1100, we now have it... How lame.

[Jesse]

This info helped me with an SSRS report... to know it bound it by position meant I had to have all parameters in the right positions. I was re-using a parameter. Tricky! So I had: startdate, enddate... then startdate, endDate, param1, param2. I needed: startdate, enddate, param1, param2... then startdate, endDate, param1, param2. Great answer! Thank you.

[jessewolfe]

We had a long-standing embedded query using ODP.NET that worked fine. Had two params A and B and the param usage in the query was A, B, A, B, A, B. We only passed in two parameters, A and B. When we modified the query and the parameter pattern became A, A, B, A, B, A, B, A, B it didn't like it anymore and wouldn't work until I set BindByName on the OracleCommand object. This is using Oracle.DataAccess 4.122.1.0 (12cR2 roughly)

[Ak777]

I am having this issue (even in 2021), and my query is simple set of selects with one param i am trying to pass via Dapper's DynamicParameters. I am using the latest Nugets except the Oracle DB version is still 11.x and not in 12. Is there any reason as to why this error may occur and solutions to solve?

[Nick.McDermaid]

That looks like SSRS?

[Caverman]

@CharlesBurns OMG....110% agree. You made my day.