This content has been marked as final. Show 4 replies
There are lots of different error messages associated with bad dates. Rather than try to catch them all, I use a BEGIN ... EXCEPTION block that contains nothing but a TO_DATE call. This is one of the rare occassions when I think "EXCEPTION WHEN OTHERS" is okay,
The following function comes from a package. If you want to make a stand-alone function, remember to say " *CREATE OR REPLACE* FUNCTION ...".
-- ******************* -- ** t o _ d t ** -- ******************* -- to_dt attempts to convert in_txt (assumed to -- be in the format of in_fmt_txt) to a DATE. -- If the conversion works, to_dt returns the DATE. -- If the conversion fails for any reason, to_dt returns in_err_dt. FUNCTION to_dt ( in_txt IN VARCHAR2 -- to be converted , in_fmt_txt IN VARCHAR2 DEFAULT 'DD-MON-YYYY' -- optional format , in_err_dt IN DATE DEFAULT NULL ) RETURN DATE DETERMINISTIC AS BEGIN -- Try to convert in_txt to a DATE. If it works, fine. RETURN TO_DATE (in_txt, in_fmt_txt); EXCEPTION -- If TO_DATE caused an error, then this is not a valid DATE: return in_err_dt WHEN OTHERS THEN RETURN in_err_dt; END to_dt ;
Thanks very much, your solution is exactly what I need. Any idea why my version does not catch the date conversion exceptions?
It looks like you were trying to do exactly what I did. Sorry, I didn't catch that before; it's very hard to read unformatted code.
If you indent the code so that every BEGIN is directly above its corresponding EXCEPTION and END, with nothing but blank space in between, you'll understand what's going on better.
You have two nested BEGIN blocks. The inner one is the only one with an EXCEPTION handler, and the only statement in the BEGIN secition is UPDATE, so only error that occur during the UPDATE will be handled. TO_DATE is called in the outer block, which has no EXCEPTION handler.
PROCEDURE date_check IS start1 DATE ; BEGIN select to_date( nvl(yearcollected,'9999') ||'/'|| nvl(monthcollected,'01') ||'/'|| nvl(daycollected,'01') ,'YYYY/MM/DD'))) into start1 from incoming_data where id=1 ; BEGIN update temp_test set test_date = start1 where id=1 ; EXCEPTION WHEN OTHERS THEN print('Date error message from exception block'); END; print('Processing continues after handling date exception') ; END date_check ;
When posting formatted text on this site, type these 6 characters:
(small letters only, inside curly brackets) before and after sections of formatted text, to preserve spacing.
I figured out what was wrong with my original idea. In spite of the fact that it makes me look pretty stupid I thought I post the corrected code in case this post turn up in a future search.
I started this small pl/sql project because we were having continual failures of a long running procedure because of date errors. The dates were in customer data so we have no choice but to try to clean the data before using it in order to avoid having to run a 24+ hr. re-indexing more than once per data cycle.
I was fixated on the fact that we were getting the exception when we tried to update existing dates. The update statement contained a to_date() function call. It did not occur to me that the to_date(), not the update, was the source of the exception. Franks reply to my question made me realize that to_date() was the culprit.
Rearranging my code to put the to_date() call in the nested block produced the behavior I was looking for, i.e. catch the date exception, issue a message and return to the outer block. The outer block will have a cursor that looks at every row of the customer data. This will result in a small procedure that will be run before the re-indexing to alert the data managers that there are invalid dates that must be corrected before re-indexing.
Frank, thanks again for the complete and non-judgmental explanation.