Are there any functions, syntax etc

[manj]

Are there any functions, syntax etc within pl/sql where I can check if a variable is numeric or a date type etc.

For example:

IF variable IS NUMERIC
do something
ELSIF variable IS DATE
do something
ELSE
do something

Cheers

 

[MikeJones]

I don't think you can do this, but why do you need to though? surely if you have defined the variable in your code you must know what type it is?

 

[manj]

The end-user is prompted to enter a value. What I need to do is to check that they have entered the correct data. Hence check that it is either a date, number etc and give a warning message to the end-user if they enter incorrect data.

 

[cdukes]

SQL Server supports the functions:
ISDATE()
ISNULL()
and ISNUMERIC()

but I am not sure about Oracle

Hope this helps

Chris Dukes

 

[CRoberts]

Not really. You must make up your own functions. If you send your variable out to your custom IsNumber function and it converts to a number, fine and return True. If it fails, then you know it is not a number and return false. Easy. But, Date is another matter. You must either force your user to enter one type of date format, or you must check all possible formats. I did something like that, had a Package that defined an array with possible date formats, then had a function that checked this array. So, the check date function could look like:

FUNCTION IsDate_Fun (InDateString IN VARCHAR2)
RETURN BOOLEAN IS
TempDate DATE;
Mask_Index PLS_INTEGER := 0;
BEGIN
Mask_Index := DateFmts.FIRST;
LOOP
EXIT WHEN Mask_Index IS NULL;
BEGIN
ReturnDate := TO_DATE
(InDateString, DateFmts(Mask_Index));
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
Mask_Index := DateFmts.NEXT (Mask_Index);
END;
END LOOP;
RETURN FALSE;
END IsDate_Fun;
/

In the Package your array could look like:

DateFmts(1) := 'DD-MON-RR';
DateFmts(2) := 'DD-MON-YYYY';
DateFmts(3) := 'DD-MON';
DateFmts(4) := 'MM/DD';
DateFmts(5) := 'MM/RR';
DateFmts(6) := 'MM/YYYY';
DateFmts(7) := 'MM/DD/RR';
DateFmts(8) := 'MM/DD/YYYY';
DateFmts(9) := 'MMDDYYYY';
DateFmts(10) := 'YYYYMMDD';
DateFmts(11) := 'RRMMDD';
DateFmts(12) := 'MMDDRR';
DateFmts(13) := 'FMMonth DD, YYYY';
DateFmts(14) := 'FMMonth ddSp, YyyySp';
DateFmts(15) := 'FMMonth ddSp, YyyySp A.D.';