Migrate Error Handling

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:

• locate 'EXCEPTION' keyword, find out if it is an user defined or standard exception
• if it is an user defined exception, delete the corresponding declaration and specify unique error code via ERRCODE in a USING clause
• in catch-block replace SQLCODE by SQLSTATE

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;
...

Have questions? Contact us