Oracle IsDate() Function

Mon, 07 Apr 2008 08:14:52 -0500

As promised in a previous post here is my IsDate() function for Oracle

I don't know how great a function it is, since I am a novice to Oracle, but it seems to work pretty well and very quickly. I learned a lot while writing it, especially about basic excpetion handling in PL/SQL. I'm really not a big fan of Oracle, or PL/SQL, but I need to know it for my work, so I hope that my struggles in things like this can help others fuddle through.

Normally, I would use ColdFusions IsDate() function and be VERY happy with it. But in this case Iam importing batches of 1,000,000 records from raw data files and running conversion scripts on them, and ColdFusion just isn't built for that kind of work, so I am doing it directly in Oracle with PL/SQL and then calling that from ColdFusion. So far so good. It is a long road, since Iam new to Oracle there is a large learning curve for me.

This Oracle function is pretty basic. It takes in a varchar2 and returns a varchar2(I would return a Boolean, but for whatever reason, in Oracle you don't get Boolean values for your $65,000 investment, and I cannot convert 1 and 0 to boolean. Stoopid Oracle). The it is the string that we would like to test.

create or replace function IsDate (str varchar2) return varchar2 is inDate varchar2(40);

Next we add a sub-function called dateCheck(). This takes in two parameters and returns one, all are varchar2 (see rant above). The first IN value is the string we passed into the parent function witht he string we want to test. The second IN parameter is the mask of the date format we want to check the string against. The return string will be either 'true' or 'false'.

FUNCTION dateCheck ( inputDate varchar2, inputMask varchar2) RETURN varchar2 IS dateVar date; BEGIN dateVar:= to_date(inputDate,inputMask); return 'true'; exception when others then return 'false'; END;

This sub-function uses the to_date() funciton in Oracle. to_date() tries to read in a string and convert it to a date using a user specified input mark. For example, to_date(06-18-2008, 'mm-dd-yyyy') would return a valid date, but to_date(06-18-2008, 'mm.dd.yyyy') would raise an exception. So since Oracle cannot return troe or false, I have create my function to retrun 'true' if the command executes successfully and 'false' if an exception is raised.

Finally, my parent function will call the child function several times using different date pattern masks to see if any of them will work. If none of them evalute to true, the overall function will reutn 'false'. If one of them wrks, then it is a valid date and the function will return 'true'.

inDate:= trim(str); if dateCheck(inDate, 'mm-dd-yyyy') = 'false' AND dateCheck(inDate, 'mm-dd-yy') = 'false' AND dateCheck(inDate, 'yyyy-mm-dd') = 'false' AND dateCheck(inDate, 'yy-mm-dd') = 'false' AND dateCheck(inDate, 'yyyy-mon-dd') = 'false' AND dateCheck(inDate, 'yy-mon-dd') = 'false' AND dateCheck(inDate, 'dd-mon-yyyy') = 'false' AND dateCheck(inDate, 'dd-mon-yy') = 'false' AND dateCheck(inDate, 'mmddyy') = 'false' AND dateCheck(inDate, 'mmddyyyy') = 'false' AND dateCheck(inDate, 'yyyymmdd') = 'false' AND dateCheck(inDate, 'yymmdd') = 'false' AND dateCheck(inDate, 'yymmdd') = 'false' AND dateCheck(inDate, 'yymondd') = 'false' AND dateCheck(inDate, 'yyyymondd') = 'false' AND dateCheck(inDate, 'mm/dd/yyyy') = 'false' AND dateCheck(inDate, 'yyyy/mm/dd') = 'false' AND dateCheck(inDate, 'mm/dd/yy') = 'false' AND dateCheck(inDate, 'yy/mm/dd') = 'false' AND dateCheck(inDate, 'mm.dd.yyyy') = 'false' AND dateCheck(inDate, 'mm.dd.yy') = 'false' AND dateCheck(inDate, 'yyyy.mm.dd') = 'false' AND dateCheck(inDate, 'yy.mm.dd') = 'false' then return 'false'; else return 'true'; end if;

So here is the overall code:

create or replace function IsDate (str varchar2) return varchar2 is inDate varchar2(40); FUNCTION dateCheck ( inputDate varchar2, inputMask varchar2) RETURN varchar2 IS dateVar date; BEGIN dateVar:= to_date(inputDate,inputMask); return 'true'; exception when others then return 'false'; END; BEGIN inDate:= trim(str); if dateCheck(inDate, 'mm-dd-yyyy') = 'false' AND dateCheck(inDate, 'mm-dd-yy') = 'false' AND dateCheck(inDate, 'yyyy-mm-dd') = 'false' AND dateCheck(inDate, 'yy-mm-dd') = 'false' AND dateCheck(inDate, 'yyyy-mon-dd') = 'false' AND dateCheck(inDate, 'yy-mon-dd') = 'false' AND dateCheck(inDate, 'dd-mon-yyyy') = 'false' AND dateCheck(inDate, 'dd-mon-yy') = 'false' AND dateCheck(inDate, 'mmddyy') = 'false' AND dateCheck(inDate, 'mmddyyyy') = 'false' AND dateCheck(inDate, 'yyyymmdd') = 'false' AND dateCheck(inDate, 'yymmdd') = 'false' AND dateCheck(inDate, 'yymmdd') = 'false' AND dateCheck(inDate, 'yymondd') = 'false' AND dateCheck(inDate, 'yyyymondd') = 'false' AND dateCheck(inDate, 'mm/dd/yyyy') = 'false' AND dateCheck(inDate, 'yyyy/mm/dd') = 'false' AND dateCheck(inDate, 'mm/dd/yy') = 'false' AND dateCheck(inDate, 'yy/mm/dd') = 'false' AND dateCheck(inDate, 'mm.dd.yyyy') = 'false' AND dateCheck(inDate, 'mm.dd.yy') = 'false' AND dateCheck(inDate, 'yyyy.mm.dd') = 'false' AND dateCheck(inDate, 'yy.mm.dd') = 'false' then return 'false'; else return 'true'; end if; --exception --when others then return 'false'; END;

Oracle IsNumeric Function

Thu, 03 Apr 2008 13:31:00 -0500

So, most people that use Oracle and other RDBMS know that they severly lack some very basic functions that exist in the middle-tier langauges we usually use. But sometimes we can;t use our middle-tier languages. Right now I am writing a Stored Procedure in Oracle that will import, validate and normalize 1,000,000 records from raw text files. This is WAY TOO big a job for ColdFusion, so I am using Oracle Stored Procudres and functions.

During my error checking I realized I needed some extra functionality. I needed IsDate() and IsNumeric(). With some Googling and simple Oracle functions, I was able to create both. today I will demostrate IsNumeric(). I am still working on adding more functionality to IsDate() and I will demo that one another day.

UPDATE: I have updated this post to reflect a better way of handling an IsNumeric() function. This is from Alexandre Paiva's comment, thank you Alexandre!


	CREATE OR REPLACE FUNCTION isnumeric (p_string_value IN VARCHAR2)RETURN BOOLEAN
	AS

	test_value NUMERIC;
	BEGIN
		BEGIN
			test_value := TO_NUMBER (p_string_value);
			RETURN (true);

			EXCEPTION
			WHEN OTHERS THEN
			RETURN (false);
		END;
	END isnumeric;

This very simple function takes in a string of characters and passes it in. Then using the Oracle TRANSLATE function, it replaces any characters that are 1-9 or a decimal, plus sign or minus sign with a space (" '). Then it does a trim on the remaining string, if the value is all spaces, the Length() function that wraps the others will return 0 and we know that the string that was passed in is numeric. If it returns 1 or more then we know there were non-numerics and we can return false.

12Robots.com - Jason Dean - Oracle

Oracle IsDate() Function

Oracle IsNumeric Function