4 Replies Latest reply on Sep 18, 2009 2:06 PM by 561854

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

    561854
      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
        • 1. Re: how to catch date errors and continue processing in a PL/SQL procedure
          Frank Kulash
          Hi, Beeky,

          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
          ;
          • 2. Re: how to catch date errors and continue processing in a PL/SQL procedure
            561854
            Frank,
            Thanks very much, your solution is exactly what I need. Any idea why my version does not catch the date conversion exceptions?

            -=beeky
            • 3. Re: how to catch date errors and continue processing in a PL/SQL procedure
              Frank Kulash
              Hi,

              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.
              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 ;
              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.

              When posting formatted text on this site, type these 6 characters:

              {code}

              (small letters only, inside curly brackets) before and after sections of formatted text, to preserve spacing.
              • 4. Re: how to catch date errors and continue processing in a PL/SQL procedure
                561854
                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.

                -=beeky