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

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3e42578c$0$246$ed9e5944_at_reading.news.pipex.net>...

> What happens if you use sqlplus? IF you can replicate it in sqlplus I'd
> suggest you consider logging a tar with oracle support.

Here's what I did; is this a bug, or am I doing something wrong?

16:45:00 SQL> -- Demonstrates that you can't use bind variables if the table

16:45:27 SQL> -- contains a column of the same name.
16:45:35 SQL> --
16:45:39 SQL> -- Set up the bind variable.
16:45:49 SQL> var ft char(4)
16:45:59 SQL> select '41' into :ft from dual;

'4

-- 
41 

Elapsed: 00:00:00.20
16:46:06 SQL> -- Let's have a look at the table.
16:46:17 SQL> desc facility_type
 Name Null? Type
 -----------------------------------------------------------------------------------------------------------------
-------- ----------------------------------------------------------------------------
[Quoted]  FACILITY_TYPE NOT NULL CHAR(4)
 DESCRIPTION NOT NULL VARCHAR2(60)
 INSERT_DATETIME DATE
 UPDATE_DATETIME DATE
 STATUS NOT NULL CHAR(1)

16:46:23 SQL> -- See, I can use the bind variable on the 'DESCRIPTION'
column...
16:46:54 SQL> select count(*) from facility_type where description <>
':ft';

 COUNT(*) 
---------- 
 46 

Elapsed: 00:00:00.20
16:47:00 SQL> -- And, I can find a known value using a literal...
16:47:23 SQL> select count(*) from facility_type where facility_type =
'41';

 COUNT(*) 
---------- 
 1 

Elapsed: 00:00:00.10
16:47:28 SQL> -- But, I can't use the bind variable to find that
value...
16:47:54 SQL> select count(*) from facility_type where facility_type =
':ft';

 COUNT(*) 
---------- 
 0 

Elapsed: 00:00:00.41
16:47:57 SQL> -- Even if I specify the table...
16:48:09 SQL> select count(*) from facility_type where
facility_type.facility_type = ':ft';

 COUNT(*) 
---------- 
 0 

Elapsed: 00:00:00.20
16:48:11 SQL> -- And even if I use a table alias...
16:48:24 SQL> select count(*) from facility_type ftt where
ftt.facility_type = ':ft';

 COUNT(*) 
---------- 
 0 

Elapsed: 00:00:00.21
16:48:26 SQL> -- The End
16:48:47 SQL> spool off

Thanks!