I have found a very interesting scenario which reflects problems you may have when you are working with Oracle. I’m using Oracle 9i and I have been trying to extract numeric data out of a list of data items, so I created a UDF (User Defined Function) and called it IsNumeric. I faced a serious problem when I wanted to use comparison operators.
My data was as follows
|
COL1
|
COL2
|
|
1
|
12
|
|
2
|
Absent
|
|
3
|
5
|
|
4
|
7 Days
|
|
5
|
7
|
|
6
|
Six Days
|
|
7
|
|
|
8
|
Un Aavalable
|
|
9
|
6
|
To create the scenario, follow the steps below.
1. Create the Test1 table.
And executed the following command
create table test1(
col1 numeric(5) primary key,
col2 varchar(20)
)
2. Insert data into the table.
insert into test1 values (1, '12');
insert into test1 values (2, 'Absent');
insert into test1 values (3, '5');
insert into test1 values (4, '7 Days');
insert into test1 values (5, '7');
insert into test1 values (6, 'Six Days');
insert into test1 values (7, '');
insert into test1 values (8, 'Un Avalable');
insert into test1 values (9, '6');
3. Create a user defined function.
create or replace function isnumeric(v in varchar2) return number as
-- returns 1 if the parameter is numeric
-- 0 non numeric
num number;
begin
num := to_number(v);
return 1;
exception
when others then
return 0;
end;
3. Enter your select statement.
select *
from test1
where isnumeric(col2) = 1
and col2 > 5
You will get the following error message.
ORA-01722: invalid number
To solve this problem
1. Create a ToNumeric function
create or replace function tonumeric(v in varchar2) return number as
-- return value if its numeric
-- 0 non numeric
num number;
begin
num := to_number(v);
return num;
exception
when others then
return null;
end;
2. Change the SQL statement this structure
select *
from test1
where isnumeric(col2) = 1
and tonumeric(col2) > 5