Handling exception in Oracle

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 ISi 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                  20001991                  199120--                      09                       911                      1195                      956 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 yFROM (    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       20001991       199120--           09            911           1195           956 rows selected. 

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.