Funtion to verify Date and number data in a table.

Following functions can be used to extract invalid data from table and are usefull in ETL.

First function accepts two parameters one is value and other is date format.
It will return 1 if the data in the column is according to date format provided else it will return 0.
This type of function can be used in following situation.

Data is loaded from excel or text file into a temporary table with all columns of character type and hiredate is loaded with mix of date entries i.e.
10/01/12,10/1/12,13-mar-2012 , 1/2/2012.
In this situation one need to identify and make same pattern of date to load data in actual table.

In following way this function can be used.


select is_date('31-mar-2013 20:10:30','dd-mon-yyyy hh24:mi:ss')
from dual;

select * from temptable
where is_date(hiredate,'dd-mon-yyyy hh24:mi:ss')<>1;

create or replace function is_date (p_value varchar2,p_format varchar2) return number is
v_date date;
begin
v_date:=to_date(p_value,p_format) ;
return 1;
exception when others then
return 0;
end;


This is same function to identify the valid number in column.

create or replace function is_number (p_value varchar2) return number is
v_date number;
begin
v_date:=p_value ;
return 1;
exception when others then
return 0;
end;


select is_number('221a34') from dual;

select * from temptable
where is_number(salary)<>1;