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

Answers


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?


Need Your Help

Possible bug with New-AzureDeployment

azure cmdlets azure-deployment azure-powershell

The New-AzureDeployment cmdlet is not updating the Deployment Name with the value from the -Name parameter. As per MSFT's documentation here, the -Name parameter maps to the Deployment Name but whe...

Java, LibGDX, Rectangle intersection fails with negative dimensions?

java libgdx intersection rectangles

I am making a unit selector for use in an RTS game, in Java with LibGDX. I have created a rectangle to check if the units hitbox collides with the hitbox of the selection, and if so the unit is add...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.