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  gt   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 lt int  foo gt  bar   new Dictionary lt int  foo gt     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

User · Accepted Answer

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.

User · Answer

Came here looking for help as got same error running a statement listed below while going through a Udemy course     INSERT INTO departments  department id  department name                    values   amp dpet id    amp dname        I d been able to run statements with substitution variables before  Comment by Charles Burns about possibility of server reaching some threshold while recreating the variables prompted me to log out and restart the SQL Developer  The statement ran fine after logging back in   Thought I d share for anyone else venturing here with a limited scope issue as mine

User · Answer

It s a bug in Managed ODP net -  Bug 21113901   MANAGED ODP NET RAISE ORA-1008 USING SINGLE QUOTED CONST   BIND VAR IN SELECT  fixed in patch 23530387 superseded by patch 24591642

User · Answer

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 lt  gt  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 lt  gt  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 lt  gt  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 lt  gt  DL      AND OHH BILL COMP CODE 100     AND OHH MASTER ORDER NBR IS NULL      Here is what the dataset parameter mapping screen looks like

User · Answer

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

User · Answer

I d a similar problem in a legacy application  but de  --  was string parameter   Ex     Dim cmd As New OracleCommand  INSERT INTO USER  name  address  photo  VALUES   User1    --    photo    oracleConnection  Dim fs As IO FileStream   New IO FileStream  c  img jpg   IO FileMode Open  Dim br As New IO BinaryReader fs  cmd Parameters Add New OracleParameter  photo   OracleDbType Blob   Value   br ReadBytes fs Length  cmd ExecuteNonQuery    here throws ORA-01008   Changing address parameter value  --  to  00  or other thing  works

User · Answer

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

User · Answer

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

[c#] ORA-01008: not all variables bound. They are bound

Examples related to c#

Examples related to oracle

Examples related to data-binding

Examples related to oracle9i

Examples related to ora-01008