CODEWITHSUNDEEP
oracle-databasevariablesparameterstoad
B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10247

Oracle and TOAD (and I) disagree on number of variables to bind

At runtime, I'm getting "ORA-01008 - not all variables bound" with a SQL statement that runs fine in TOAD and in which TOAD (and I) only find one parameter.

As can be seen below, there IS only one parameter in the query. Why would Oracle think there are more than one parameters/variables and cause an exception to be thrown?

I cannot show the real sql, but here is a facsimile of it (column/table names changed):

SELECT DECODE(POSTWHEELTYPE,'0','NONE','D','NUMERIC','D','RESTRICTED NEEDLE','X','TRANSFER TO WINTER','A','ACCESS CODE') HALTYPE,      
    DECODE(VALIDATIONTYPE,'0','NONE','A','FOXPRO CODE','P','PERSONAL CODE','S','RESTRICTED') JBJTYPE,
    LAZYNUMBER,
    DISPLAYTEXT,
    MINLENGTH || '-' || MAXLENGTH LENGTH,
    NVL(INSTRUCTIONS, '<NONE>') INSTRUCTIONS
FROM
    ABC.CODELAZYS
WHERE
    BQSERVERABCID = :ABCID
    AND VALIDATIONTYPE <> '0'
ORDER BY
    LAZYNUMBER DESC

Upvotes: 0

Views: 407

Answers (2)

B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10247

The problem was that I was binding the variable too late; once I moved it above the call to ExecuteReader(), it worked.

Upvotes: 0

DCookie
DCookie

Reputation: 43523

If Oracle is returning this error code, I would bet the better part of my last dollar that the variable was not bound. Just because TOAD has provided a value for the variable does not mean that it's bound in other clients.

Are you 100% positive your one variable is indeed bound? Where are you running the query that fails? Can you show us how it's called?

Upvotes: 2

Related Questions