CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
user1279970
user1279970

Reputation:

Converting a String to Date and raising an exception when given String is invalid

Here is my code given below. I want if the date given is incorrect it should display a message that is "The format provided is incorrect".

But I cant get it to raise the exception.
Is the exception used incorrect which other exception provided by oracle can I use for this case?

Create or Replace Procedure A1SF_TESTDATE
(
    pDateStr Varchar2
)As
    tDate Date;
    Begin
    tdate := TO_DATE(pDateStr, 'yyyymmdd');
    dbms_output.put_line(tdate);
    Exception 
      When INVALID_NUMBER Then
        dbms_output.put_line('The format provided is incorrect');

End;

Upvotes: 1

Views: 4416

Answers (1)

Tim
Tim

Reputation: 2831

There are many exception which can be thrown by the TO_DATE function. Examples:

  • ORA-01843 - Invalid month value
  • ORA-01847 - Invalid day value
  • ORA-01830 - date format picture ends before converting entire input string
  • ...

You can catch them as in the following example (with only one exception):

Create or Replace Procedure A1SF_TESTDATE
(
    pDateStr Varchar2

    -- you must do this for every oracle exception number which will you catch
    bad_month EXCEPTION;
    PRAGMA EXCEPTION_INIT (bad_month, -01843);
)As
    tDate Date;
    Begin
    tdate := TO_DATE(pDateStr, 'yyyymmdd');
    dbms_output.put_line(tdate);
    Exception 
      When bad_month Then
        dbms_output.put_line('The format provided is incorrect');

End;

But for that you must define n pragmas!

The easier solution, which I prefer, is: 

Create or Replace Procedure A1SF_TESTDATE
    (
        pDateStr Varchar2
    )As
        tDate Date;
Begin
        tdate := TO_DATE(pDateStr, 'yyyymmdd');
        dbms_output.put_line(tdate);
        Exception 
          -- every exception will be catched
          When others Then
            dbms_output.put_line('The format provided is incorrect! Because: ' || SQLERRM);

 End;

A possible message for SQLERRM is ORA-01847: day of month must be between 1 and last day of month.

Upvotes: 3

Related Questions