Re: ORA:01839 [message #38499] |
Tue, 23 April 2002 02:36 |
John R
Messages: 156 Registered: March 2000
|
Senior Member |
|
|
Well to handle the error, add an exception block to your function.
I think the problem runs a little deeper than that though.
If I read your code correctly, you have written a quite intricate function to accept a character string, check if that string holds a date in a predefined format. If it does, return the string you were passed, otherwise return the date '31 Dec 9999'.
This could be more succinctly written thus:
CREATE OR REPLACE FUNCTION single_date (p_date IN varchar2) RETURN varchar2 AS
l_date date;
BEGIN
l_date := to_date(p_date,'yyyymmdd');
RETURN p_date;
EXCEPTION
WHEN OTHERS THEN
RETURN ('99991231');
END;
|
|
|