title: Handling exception in Oracle
body: <p>I'm trying to parse years entered as strings (<em>please don't get me started - it is what it is</em>). There are years however which are entered that can't be parsed by <code>TO_NUMBER</code>.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I've never done exception handling in Oracle so apologies if this is such a basic question.</p>
  
  <p>When running my query above I get <code>ORA-00928: missing SELECT keyword</code> and then it highlights the <code>BEGIN</code> keyword. From searching around all I've seen people do is use <code>BEGIN SELECT</code> which is also what I'm doing. I'm guessing I messed up somewhere else?</p>
  
  <p>Basically what I want to do is parse the string and if an exception is thrown I'll just set it to <code>NULL</code>.</p>
  
  <h2>EDIT</h2>
  
  <p>I tried a different approach and adding some semi-colons as @DavidFaber commented out below.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>I get a different error now <code>ORA-06550: line 2, column 3: PLS-00428: an INTO clause is expected in this SELECT statement</code>.</p>