Handling exception in Oracle

Question

I'm trying to parse years entered as strings (please don't get me started - it is what it is). There are years however which are entered that can't be parsed by TO_NUMBER.

WITH src AS (
        SELECT '2000' AS y FROM DUAL
  UNION SELECT '1991' AS y FROM DUAL
  UNION SELECT '20--' AS y FROM DUAL
  UNION SELECT '09' AS y FROM DUAL
  UNION SELECT '11' AS y FROM DUAL
  UNION SELECT '95' AS y FROM DUAL
)

BEGIN
  SELECT
    s.y,
    TO_NUMBER(s.y) AS p
  FROM src s
EXCEPTION
  WHEN INVALID_NUMBER THEN NULL
END

I've never done exception handling in Oracle so apologies if this is such a basic question.

When running my query above I get ORA-00928: missing SELECT keyword and then it highlights the BEGIN keyword. From searching around all I've seen people do is use BEGIN SELECT which is also what I'm doing. I'm guessing I messed up somewhere else?

Basically what I want to do is parse the string and if an exception is thrown I'll just set it to NULL.

EDIT

I tried a different approach and adding some semi-colons as @DavidFaber commented out below.

BEGIN
  SELECT
    s.y,
    TO_NUMBER(s.y) AS p
  FROM (
          SELECT '2000' AS y FROM DUAL
    UNION SELECT '1991' AS y FROM DUAL
    UNION SELECT '20--' AS y FROM DUAL
    UNION SELECT '09' AS y FROM DUAL
    UNION SELECT '11' AS y FROM DUAL
    UNION SELECT '95' AS y FROM DUAL
  ) s;
EXCEPTION
  WHEN INVALID_NUMBER THEN NULL;
END;

I get a different error now ORA-06550: line 2, column 3: PLS-00428: an INTO clause is expected in this SELECT statement.

Answer 1

There's no exception-handling in SQL;

Yes that's true, but there is workaround like inline function (Oracle 12c):

WITH FUNCTION safe_to_NUMBER(input IN VARCHAR2)
RETURN NUMBER IS
i NUMBER;
BEGIN
  i:= TO_NUMBER(input);

  RETURN i;
  EXCEPTION
  WHEN OTHERS THEN 
    RETURN NULL;
END;
SELECT  sub.y, safe_to_NUMBER(sub.y)
FROM (
    SELECT '2000' AS y FROM DUAL UNION ALL
    SELECT '1991' AS y FROM DUAL UNION ALL
    SELECT '20--' AS y FROM DUAL UNION ALL
    SELECT '09' AS y FROM DUAL UNION ALL
    SELECT '11' AS y FROM DUAL UNION ALL
    SELECT '95' AS y FROM DUAL
  ) sub;

Result:

Y    SAFE_TO_NUMBER(SUB.Y)
---- ---------------------
2000                  2000
1991                  1991
20--                      
09                       9
11                      11
95                      95

6 rows selected. 

Of course I wouldn't write such production code :)

---

Correct way (DEFAULT NULL ON CONVERSION ERROR - available starting from Oracle12cR2):

SELECT sub.y, TO_NUMBER(sub.y DEFAULT NULL ON CONVERSION ERROR) AS y
FROM (
    SELECT '2000' AS y FROM DUAL UNION ALL
    SELECT '1991' AS y FROM DUAL UNION ALL
    SELECT '20--' AS y FROM DUAL UNION ALL
    SELECT '09' AS y FROM DUAL UNION ALL
    SELECT '11' AS y FROM DUAL UNION ALL
    SELECT '95' AS y FROM DUAL
  ) sub;

Output:

Y             Y
---- ----------
2000       2000
1991       1991
20--           
09            9
11           11
95           95

6 rows selected. 

Answer 2

There's no exception-handling in SQL; you'll need to create a PL/SQL block to handle the exception (note that I changed your UNIONs to UNION ALL):

BEGIN
  WITH src AS (
    SELECT '2000' AS y FROM DUAL UNION ALL
    SELECT '1991' AS y FROM DUAL UNION ALL
    SELECT '20--' AS y FROM DUAL UNION ALL
    SELECT '09' AS y FROM DUAL UNION ALL
    SELECT '11' AS y FROM DUAL UNION ALL
    SELECT '95' AS y FROM DUAL
  )
  SELECT s.y, TO_NUMBER(s.y) AS p
    FROM src s;
EXCEPTION
  WHEN INVALID_NUMBER THEN NULL;
END;
/

But rather than use a PL/SQL block you could use regular expressions to perform a "safe" number conversion:

  WITH src AS (
    SELECT '2000' AS y FROM DUAL UNION ALL
    SELECT '1991' AS y FROM DUAL UNION ALL
    SELECT '20--' AS y FROM DUAL UNION ALL
    SELECT '09' AS y FROM DUAL UNION ALL
    SELECT '11' AS y FROM DUAL UNION ALL
    SELECT '95' AS y FROM DUAL
  )
  SELECT s.y, TO_NUMBER(REGEXP_SUBSTR(s.y, '^\d+'))
    FROM src s;

The above will convert the value 20-- to 20 which may not be what you want - in which case try with this pattern ^\d+$ instead:

  WITH src AS (
    SELECT '2000' AS y FROM DUAL UNION ALL
    SELECT '1991' AS y FROM DUAL UNION ALL
    SELECT '20--' AS y FROM DUAL UNION ALL
    SELECT '09' AS y FROM DUAL UNION ALL
    SELECT '11' AS y FROM DUAL UNION ALL
    SELECT '95' AS y FROM DUAL
  )
  SELECT s.y, TO_NUMBER(REGEXP_SUBSTR(s.y, '^\d+$'))
    FROM src s;

Hope this helps.