Oracle Errors Handling

Handling PL/SQL Errors: Catch an exception and don't throw it

Either:

BEGIN
  do something
EXCEPTION WHEN OTHERS THEN
  IF SQLCODE = -30 THEN
    NULL; -- ignore this particular exception
  ELSE
    RAISE; -- but throw the others
  END IF;
END; 

Or:

DECLARE
  ORA00030 EXCEPTION; -- or any other name you like instead of ORA00030
  pragma EXCEPTION_INIT(ORA00030, -30); -- link your exception name to the code
BEGIN
  do something
EXCEPTION WHEN ORA00030 THEN
  NULL; -- ignore this particular exception
END;

Handling exception in Oracle

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.

Different default error handling in Oracle and PostgreSQL

Oracle and PostgreSQL indeed behave differently here.

Oracle has something that I would call “statement-level rollback”: if a statement running inside a transaction causes an error, only the effects of that statement are rolled back, and the transaction continues.

In PostgreSQL, any error inside a transaction aborts the whole transaction, so you can only roll back the transaction, and it has no effects at all. This is more in the spirit of “all or nothing”, but as far as I can see, the SQL standard is not specific about this, so both can behaviors can be argued.

You can, however, use standard conforming savepoints in PostgreSQL to “recover” from an error in a transaction:

START TRANSACTION;

INSERT INTO table1 VALUES (1);

/* set a savepoint we can revert to */
SAVEPOINT x;

CALL raise_error();

ROLLBACK TO SAVEPOINT x;

/* now the INSERT can be committed */
COMMIT;

But be warned that you don't use too many savepoints (not more than 64) per transaction, else performance may suffer.

---

Related Topics