Function to Check Data Type 1

[tijimathew]

Dear all

Could any one tell me if there an inbuilt function to check the data type of an input quantity through ORACLE SQL Query. I am not verifying any datebase column data type. I am verifying a prompt variable data type based on which I need to output sysdate or a the selected date.

Thanks & Regards
Tiji Mathew

 

[SantaMufasa]

Tiji,

If there is not an built-in function to handle your needs, I believe we can build a user-defined function. Please specify in a reply a representative sampling of the different types of input possibilities, and the results you would like returned for each sample.

Dave
Sandy, Utah, USA @ 17:27 GMT, 10:27 Mountain Time

 

[tijimathew]

I am working in Datawarehousing and the application (Business Objects) has thsi functinality to check if the input value is a valid date or not.

eg. Isnumber("12312&quot = True
and Isnumber("DJHSDJ&quot = False

Similarily, I am expecting a function
Isdate(" &quot = False
Isdate("09/23/2003&quot = True

My Requirement is to have the date retunrned as SYSDATE in case the user does not input anything.

Is there a built in Function in Oracle that I can use.
e.g.
Select
Case When Isdate(MyVariable) = true then To_date(Myvariable,'mm/dd/yyyy')
else SYSDATE
end
from Dual;

Appreciate your quick response.

Thanks & Regards
Tiji Mathew

 

[SantaMufasa]

Tiji,

Although Oracle has no such built-in of which I am aware, you are welcome to use my function, below, and modify it according to your needs. In its current incarnation, the function returns varchar2 results in as slight variation ('27-OCT-2003') of Oracle's default date format for input values that are all-numeric, with or without field separators (-,/, or space), as in your example(s), above. (BTW, if the character length of the function result = 11 [example: length(date_ck(date_in))=11], then a valid date resulted from the function]:

=========================================

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor "Dasages, LLC" makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM 
REM Please contact the author via email (dave@dasages.com) when 
REM you have comments, suggestions, and/or difficulties with this
REM package or its functions.
REM
REM [Please keep the above disclaimer and the embedded electronic 
REM  documentation with this script.]
REM **************************************************************
create or replace function date_ck (dt_in in varchar2) return varchar2 is
	delim1_pos	number;
	delim2_pos	number;
	delim3_pos	number;
	mon_num		number;
	day_num		number;
	year_num	number;
	dt_hold		varchar2(50);
	dt_temp		varchar2(50);
	dayyear_hold	varchar2(50);
	month_days	varchar2(24)	:= '312831303130313130313031';
	function dh_char (str in varchar2, len in number) return varchar2 is
		hold_str varchar2(50)	:= '00000000000000000000000000'||str;
		hold_len number;
	begin
		hold_len	:= length(hold_str);
		return substr(hold_str,hold_len-(len-1),len);
	end;
	function ambiguous_dt return varchar2 is
		str_hold	varchar2(100);
	begin
		str_hold := dt_hold||' Error: date is ambiguous.('||
			to_char(to_date('0'||dt_hold,'mmddrr'),'dd-MON-yyyy')||','||
			to_char(to_date(substr(dt_hold,1,2)||'0'||substr(dt_hold,3),'mmddrr'),
				'dd-MON-yyyy')||')';
		return str_hold;
	end;
begin
	delim1_pos	:= 0;
	delim2_pos	:= 0;
	dt_hold		:= translate(dt_in,'/. ','---');
	delim1_pos	:= instr(dt_hold,'-');
	dt_temp		:= translate(dt_hold,'0123456789','----------');
	if length(rtrim(dt_temp,'-')) > 0 then
		return dt_in||' Error: 0-9,/,-,<sp> chrs. only.';
	end if;
	if delim1_pos > 0 then
		delim2_pos	:= instr(dt_hold,'-',delim1_pos+1);
		if delim2_pos = 0 then
			return dt_in||' Error: delimeters must be in pairs.';
		else
			delim3_pos	:= instr(dt_hold,'-',delim2_pos+1);
			if delim3_pos > 0 then
				return dt_in||' Error: only two delimeters allowed.';
			end if;
		end if;
		mon_num		:= substr(dt_hold,1,delim1_pos-1);
		day_num		:= substr(dt_hold,delim1_pos+1,delim2_pos-delim1_pos-1);
		year_num	:= substr(dt_hold,delim2_pos+1);
		if year_num between 0 and 49 then
			year_num := year_num + 2000;
		elsif	year_num between 50 and 99 then
				year_num := year_num + 1900;
		elsif	year_num between 1900 and to_char(sysdate,'yyyy') then
				null;
		else
			return dt_in||' Error: year must be 1900 - '||
				to_char(sysdate,'yyyy')||'.';
		end if;
		if mon_num not between 1 and 12 then
			return dt_in||' Error: bad month number.';
		end if;
		if day_num not between 1 and substr(month_days,((mon_num-1)*2)+1,2) then
			if mon_num = 2 and mod(year_num,4)=0 and day_num = 29 then
				null;
			else
				return dt_in||' Error: bad day number.';
			end if;
		end if;
		begin
			return to_char(to_date(dh_char(mon_num,2)||dh_char(day_num,2)||
				dh_char(year_num,4),'mmddyyyy'),'dd-MON-YYYY');
		exception
			when others then
				return dt_in||': '||sqlerrm;
		end;
	else
		-- Month validation
		if substr(dt_hold,1,1) = 0 then
			if substr(dt_hold,2,1) = 0 then
				return dt_in||' Error: bad month number.';
			end if;
		elsif substr(dt_hold,1,1) = 1 then
			if length(dt_hold) = 4 then
				null;
				dt_hold	:= '0'||dt_hold;
			elsif length(dt_hold) = 5 then
				if substr(dt_hold,2,1) between 0 and 2 then
					return ambiguous_dt;
				elsif substr(dt_hold,2,2) > 31 then
					return dt_in||' Error: bad day number.';
				end if;
				dt_hold	:= '0'||dt_hold;
			elsif length(dt_hold) = 6 then
				if substr(dt_hold,2,1) between 1 and 2 and
				   substr(dt_hold,3,4) between 1990 and
						to_char(sysdate,'yyyy') then
					return ambiguous_dt;
				end if;
			end if;
		else
			dt_hold	:= '0'||dt_hold;
		end if;
		mon_num	:= substr(dt_hold,1,2);
		-- Day/Year validation
		DayYear_hold := substr(dt_hold,3);
		if length(DayYear_hold)=3 then
			day_num		:= substr(DayYear_hold,1);
			year_num	:= Substr(DayYear_hold,2);
		elsif length(DayYear_hold)=4 then
			day_num		:= substr(DayYear_hold,1,2);
			year_num	:= substr(DayYear_hold,3);
		elsif length(DayYear_hold)=5 then
			day_num		:= substr(DayYear_hold,1,1);
			year_num	:= substr(DayYear_hold,2);
		elsif length(DayYear_hold)=6 then
			day_num		:= substr(DayYear_hold,1,2);
			year_num	:= substr(DayYear_hold,2);
		end if;
		if year_num between 0 and 49 then
			year_num := year_num + 2000;
		elsif	year_num between 50 and 99 then
				year_num := year_num + 1900;
		end if;
		begin
			return to_char(to_date(dh_char(mon_num,2)||dh_char(day_num,2)||
				dh_char(year_num,4),'mmddyyyy'),'dd-MON-YYYY');
		exception
			when others then
				return dt_in||': '||sqlerrm;
		end;
	end if;
end;
/
=========================================
Sample test values:
select * from dates;
01011999
01/01/1999
01-01-1999
1/1/1999
112999
010199
01/01/99
01/01/00
11999
1/1/00
12399
01011999 01211999
02/29/2006
02/29/01
13/01/01
12/0101
02/29/00
28-JUL-2003
=========================================
Sample function results:
select date_ck(date_in) from dates;
01-JAN-1999
01-JAN-1999
01-JAN-1999
01-JAN-1999
29-NOV-1999
01-JAN-1999
01-JAN-1999
01-JAN-2000
11999 Error: date is ambiguous.(19-JAN-1999,09-NOV-1999)
01-JAN-2000
12399 Error: date is ambiguous.(23-JAN-1999,03-DEC-1999)
01011999 01211999 Error: delimeters must be in pairs.
02/29/2006 Error: year must be 1900 - 2003.
02/29/01 Error: bad day number.
13/01/01 Error: bad month number.
12/0101 Error: delimeters must be in pairs.
29-FEB-2000
28-JUL-2003 Error: 0-9,/,-,<sp> chrs. only.
=========================================[code]

Let me know how closely it comes to your needs. I'm happy to modify it for you if you itemise the functional changes you would need.

Cheers,

Dave
Sandy, Utah, USA @ 18:38 GMT, 11:38 Mountain Time

 

[tijimathew]

Dear Dave

Appreciate your Co operation in this regard. Although the function perfectly fits into my requirement. The current Database does not allow one to run user defined function.

I guess I will have to manage with the application level function.

Thanks For your Help.

Best Regards
Tiji Mathew

 

[SantaMufasa]

Tiji,

Do you mean that current "application restrictions" do not allow you to run user-defined functions? Because if you are using Oracle7 or later, there certainly is no "Database" proscription. If this is the case, what is the business/IT rationale for restricting the use of user-defined functions?

Dave
Sandy, Utah, USA @ 22:01 GMT, 15:01 Mountain Time

 

[tijimathew]

Hi Dave

Only OLTP Systems have privileges given to developers to run the procedueres. The use of Stored procedure or Functions have ben disabled with the rationale of managing such a large databse and avoiding tampering of data.

Thanks for you help Dave.

Regards
Tiji