Home > Documentation > Oracle to PostgreSQL

Migrate handling errors

Oracle allows user-defined exceptions on type/class level. PostgreSQL just allows to specify error message and code. Oracle stores error code in SQLCODE, while PostgreSQL uses SQLSTATE for the same purpose.

This is how errors handling block of code should be migrated from Oracle to PostgreSQL:

This example illustrates migration of user-defined error. In Oracle:

...
TRACK_EXCEPTION EXCEPTION;
BEGIN
...
    IF TRACK_DATA IS NULL THEN
         RAISE TRACK_EXCEPTION;
    END IF;
...
    EXCEPTION
      WHEN TRACK_EXCEPTION THEN
        DBMS_OUTPUT.PUT_LINE('Track too long');
        RETURN NULL;
...

PostgreSQL equivalent:

...
BEGIN
...
    IF TRACK_DATA IS NULL THEN
         RAISE USING ERRCODE = '20001';
    END IF;
...
    EXCEPTION
      WHEN SQLSTATE '20001' THEN 
        RAISE NOTICE 'Track too long';
        RETURN NULL;
...