Re: Table and column with same name, fails only if using bind parameter

Spam_at_DefinitiveSolutions.com said...
> I have a table called PRICING_LEVEL, which also has a column called
> PRICING_LEVEL. (No, I'm not the one that did this.)
>
> The problem is, while this correctly returns the one row I expect:
>
> SELECT description
> FROM pricing_level
> WHERE pricing_level.pricing_level = '41';
>
> ... this always incorrectly returns zero rows:
>
> SELECT description
> FROM pricing_level
> WHERE pricing_level.pricing_level = :p_price_lev;
>
> That is, when I use bind parameters, it fails to return the correct
> row; no error is generated. Interestingly, it fails using either a
> CRecordset-derived class (ODBC), or an ODynaset-derived class (oo4o).
>
> The workaround is, of course, to not use bind parameters for this
> particular query, but I'm wondering if anyone could help me understand
> exactly *why* this is failing. Obviously Oracle is getting confused
> (understandably) by the naming confusion, but I'm looking for a way to
> understand how and why. Any input is appreciated. TIA.
>

Don't let Tom Kyte hear you say "not use bind parameters." :)

How are you providing the value '41' for :p_price_lev at runtime? Can it ever be a null?

You say no error is generated. A common thing I've seen among our developers (not saying you'd do that, of course) is that they check for a few cases when they throw an exception, then ignore any other errors (kinda like coding a PL/SQL exception handler with a WHEN OTHERS THEN NULL). BTW, I don't think Oracle is getting confused by having a table and column named the same. Otherwise, your first SELECT wouldn't work either.

-- 
/Karsten