CODEWITHSUNDEEP
oracle-databaseplsql
Matthew Moisen
Matthew Moisen

Reputation: 18289

How to catch TO_TIMESTAMP or TO_DATE exceptions in PLSQL without an OTHERS clause?

I noticed that TO_TIMESTAMP raises a variety of error codes depending on how it failed;

--01847
select to_timestamp('2016-01-0a', 'yyyy-MM-dd') from dual;
--01858
select to_timestamp('2016-01-aa', 'yyyy-MM-dd') from dual;
--01843
select to_timestamp('2016-15-01', 'yyyy-MM-dd') from dual;

Given that it doesn't raise a single error code, how can I catch the error without resorting to using the catch-all OTHERS:

DECLARE
    foo timestamp;
BEGIN
    select to_timestamp('2016-01-0a', 'yyyy-MM-dd') into foo from dual;
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('bad date');
END;

Upvotes: 2

Views: 2919

Answers (1)

alexgibbs
alexgibbs

Reputation: 2480

If you are on 12cR2, then you can use VALIDATE_CONVERSION to check the validity of a potential type conversion before making the conversion and avoid the exception entirely.

In earlier versions, you could create your own equivalent function for TIMESTAMPs that attempts the conversion and handles the exceptions. You could declare each exception-type of interest individually and log/handle the failure-modes of interest separately for each as needed, with an overall WHEN OTHERS for the stuff you don't care about or want to log/handle:

CREATE OR REPLACE FUNCTION IS_TIMESTAMP_FORMAT_OK(P_TIMESTAMP_TEXT IN VARCHAR2, P_FORMAT IN VARCHAR2 DEFAULT 'yyyy-MM-dd')
  RETURN BOOLEAN
IS
  --exception names
  V_TIMESTAMP TIMESTAMP;
    DAY_OF_MONTH EXCEPTION;
    NON_NUMERIC EXCEPTION;
  --etc.
PRAGMA EXCEPTION_INIT (DAY_OF_MONTH, -1847);
PRAGMA EXCEPTION_INIT (NON_NUMERIC, -1858);
  BEGIN
    V_TIMESTAMP := to_timestamp(P_TIMESTAMP_TEXT, P_FORMAT);
    RETURN TRUE;
    EXCEPTION WHEN DAY_OF_MONTH
    THEN
    DBMS_OUTPUT.PUT_LINE('The day of month must be between...');
    RETURN FALSE;
    WHEN NON_NUMERIC
    THEN
    DBMS_OUTPUT.PUT_LINE('Non-Numeric data was found...');
    RETURN FALSE;
    --etc.
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(UTL_LMS.FORMAT_MESSAGE('Unexpected timestamp problem: %s', SQLERRM));
    RETURN FALSE;
  END;
/

Then you can log/handle the types of interest:

DECLARE
  V_CHECK BOOLEAN;
BEGIN
  V_CHECK := IS_TIMESTAMP_FORMAT_OK('2016010a');
  V_CHECK := IS_TIMESTAMP_FORMAT_OK('2016-01-aa');
  V_CHECK := IS_TIMESTAMP_FORMAT_OK('2016-01-0a');
  IF IS_TIMESTAMP_FORMAT_OK('2014-01-01')
  THEN
    DBMS_OUTPUT.PUT_LINE('It is ok.  Yay');
  END IF;
END;
/

Unexpected timestamp problem: ORA-01862: the numeric value does not match the
length of the format item
Non-Numeric data was found...
The day of month must be between...
It is ok.  Yay

Or if you don't care about logging/handle different failure modes and just want to prevent broad exception-catching, you can go ahead and use WHEN OTHERS, but in isolated scope:

CREATE OR REPLACE FUNCTION 
  IS_TIMESTAMP_FORMAT_OK(P_TIMESTAMP_TEXT IN VARCHAR2, P_FORMAT IN VARCHAR2 DEFAULT 'yyyy-MM-dd')
  RETURN BOOLEAN
IS
  V_TIMESTAMP TIMESTAMP;
  BEGIN
    V_TIMESTAMP := to_timestamp(P_TIMESTAMP_TEXT, P_FORMAT);
    RETURN TRUE;
    EXCEPTION WHEN OTHERS THEN
    RETURN FALSE;
  END;
/

Or Inline:

DECLARE
  V_MY_TIMESTAMP TIMESTAMP;
  BEGIN
  -- some other code ...

  BEGIN
    V_MY_TIMESTAMP := to_timestamp('2016-01-aa', 'yyyy-MM-dd');
    EXCEPTION WHEN OTHERS THEN NULL;
  END;

  DBMS_OUTPUT.PUT_LINE('My Timestamp:'||V_MY_TIMESTAMP);

END;
/

My Timestamp:

Upvotes: 2

Related Questions