Saturday, November 5, 2011

   Treatment of Oracle Data Type

Oracle offers many data types such as char, varchar2, date, number, LOB, XML, timestamp
etc. However, many applications start using varchar2 data type for numeric or date values.
The main reason they say is data is coming from another system and they have no control
over incoming data. This article explains the consequences of using wrong data type in
Oracle.


First, I create a table that has one column with varchar2 data type.

SQL> create table mytable (col1 varchar2(20))  ;

Table created.

SQL> insert into mytable values (1)  ;  -- inserted numeric value

1 row created.

SQL> insert into mytable values ('2')  ;  -- inserted numeric value but used quote

1 row created.

SQL> commit;

Commit complete.

SQL>  select * from mytable;

COL1
--------------------
1
2

SQL> select * from mytable where col1 = 1;

COL1
--------------------
1

SQL> select * from mytable where col1 = '2'  ;

COL1
--------------------
2

So far, no problem because all the rows have numeric value and the filter condition has
numeric value.

Let us see what happens when I insert character value.

SQL> insert into mytable values ('a')  ;

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mytable where col1 = '1'  ;

COL1
--------------------
1                     -- No problem

SQL> select * from mytable where col1 = '2'  ;

COL1
--------------------
2                   -- No problem

SQL> select * from mytable where col1 = 1  ;
ERROR:
ORA-01722: invalid number

no rows selected


Oops !!. The query that was working earlier is not working now.
Implicit conversion of 'a' to a numeric value failed.


Let us flush the shared pool and try again.

SQL> alter system flush shared_pool;

System altered.

SQL>  select * from mytable where col1 = 1  ;
ERROR:
ORA-01722: invalid number

no rows selected

SQL>

!!! NO LUCK !!!

Conclusion:

Always use correct data type in the WHERE clause, otherwise your application may not
work as you expected. Do not expect oracle will do "implicit" conversion of data all the time.

Cheers, Tamil

No comments:

Post a Comment