Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

how to catch date errors and continue processing in a PL/SQL procedure

561854Sep 17 2009 — edited Sep 18 2009
I'm updating a date field with dates constructed from day, month and year fields. The incoming data has many instances of day and month that are not valid dates, ex 11 31 2007. There is no 31st day in November.

I would like to write a pl/sql script to scan the table containing these values and log the rows that produce conversion errors.

I thought I could do this with exceptions but there are no exceptions that correspond to the ORA-01847 error for mismatched day and month.

Here is what I tried (the print procedure is a local wrapper for DBMS_OUTPUT.put_line):

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 ;

Is there a way to catch this kind of error and continue processing after logging a message?

-=beeky
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 16 2009
Added on Sep 17 2009
4 comments
4,755 views