Re: Char Cast to Numeric ?

Buck Turgidson wrote:
>
> I am wondering if someone can tell me why the following select
> statement works against a numeric column, both with the 6 as a number
> and as a char ('6')?
>
> Connected to:
> Oracle7 Server Release 7.3.4.3.0 - Production
> With the distributed, replication and parallel query options
> PL/SQL Release 2.3.4.3.0 - Production
>
> SQL> create table temp_01 (sequence_nbr number)
> 2 /
>
> Table created.
>
> SQL>
> SQL> insert into temp_01
> 2 select rownum
> 3 from user_objects
> 4 where rownum < 10
> 5 /
>
> 9 rows created.
>
> SQL>
> SQL>
> SQL> select * from temp_01
> 2 where sequence_nbr = 6
> 3 /
>
> SEQUENCE_NBR
> ------------
> 6
>
> SQL>
> SQL> select * from temp_01
> 2 where sequence_nbr = '6'
> 3 /
>
> SEQUENCE_NBR
> ------------
> 6

Oracle will attempt in most cases to do datatype conversion. This yields nasty things like:

select *
from table
where indexed_varchar2_col = 123;

will NOT use the index, because wraps a "to_number( )" around the column to get a datatype match.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."