Re: Table and column with same name, fails only if using bind parameter
Date: Fri, 7 Feb 2003 17:21:55 -0000
Message-ID: <3e43eb35$0$4022$ed9e5944_at_reading.news.pipex.net>
My first reaction is that it looks like you have some type conversion going on see my demo below paying attention to where the ' symbols go.
SQL> set echo on
SQL> create table facility_type(
2 FACILITY_TYPE CHAR(4),
3 DESCRIPTION VARCHAR2(60),
4 INSERT_DATETIME DATE,
5 UPDATE_DATETIME DATE,
6 STATUS CHAR(1));
Table created.
SQL> SQL> /* insert some data */ SQL> begin
2 for i in 1..1000 loop
3 insert into facility_type values(i,i,sysdate,sysdate,'Y'); 4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> SQL> var ft char(4); SQL> exec :ft:='41';
PL/SQL procedure successfully completed.
SQL>
SQL> select count(*) from facility_type where description <> ':ft';
COUNT(*)
1000
SQL>
SQL> select count(*) from facility_type where facility_type = '41';
COUNT(*)
1
SQL>
SQL> select count(*) from facility_type where facility_type = ':ft';
COUNT(*)
0
SQL>
SQL> select count(*) from facility_type where facility_type =:ft;
COUNT(*)
1
SQL>
SQL> print :ft;
FT
41
SQL> SQL> SQL> spool off
-- Niall Litchfield Oracle DBA Audit Commission UK "Larry Leonard" <Spam_at_DefinitiveSolutions.com> wrote in message news:27270a53.0302061354.439daf07_at_posting.google.com...Received on Fri Feb 07 2003 - 18:21:55 CET
> "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
> -------------------------------------------------------------------------
----------------------------------------
> -------- -----------------------------------------------------------------
-----------
> 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!