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
test_value := TO_NUMBER (p_string_value);
WHEN OTHERS THEN
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.