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.

Which version do you have? Maybe [DEFAULT ... ON CONVERSION ERROR](https://stackoverflow.com/a/45886745/5070879) or [VALIDATE_CONVERSION](https://stackoverflow.com/questions/28819709/how-can-you-tell-if-a-value-is-not-numeric-in-oracle/45886915#45886915) — Lukasz Szozda, Jun 06 '18 at 16:17
You appear to be missing some semicolons - this isn't a simple SQL query, it is PL/SQL — David Faber, Jun 06 '18 at 16:18
@lad2025 That seems like a good solution but we're on `11g` so it looks like I can't use that. — dokgu, Jun 06 '18 at 16:18
One thing to note though is that It is always better to parse these dates (year in your case) in your web application and only store and operate with date types inside database and it's associated code. Importantly, never ever store dates and timestamps as strings in databases, great if you have not done or planning to do that anyway. — Kaushik Nayak, Jun 06 '18 at 16:49
@KaushikNayak I need to parse these years within the query because I'm generating reports with date parameters. And yes I always use the correct data types, this is just a really old system we're still maintaining. Not designed the best way but it is what it is. — dokgu, Jun 06 '18 at 17:34

Lukasz Szozda · Answer 1 · 2018-06-06T17:57:23.443

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.

David Faber · Accepted Answer · 2018-06-06T16:24:23.400

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.

I just want to add that one workaround with Oracle 12c could be `WITH FUNCTION` and then I could handle it within inline function. — Lukasz Szozda, Jun 06 '18 at 16:22
I ran this from within PL/SQL and I get an error `ORA-06550: line 2, column 3: PLS-00428: an INTO clause is expected in this SELECT statement`. — dokgu, Jun 06 '18 at 16:23
@uom-pgregorio, yes, sorry about that. You would have to `SELECT ... INTO` and then print out the results, or do an update, etc. But you don't need a PL/SQL block to accomplish this; see my edit. — David Faber, Jun 06 '18 at 16:25

Handling exception in Oracle

EDIT

2 Answers2