Migrating Exception Handling from Oracle to YugabyteDB

When migrating PL/SQL code from Oracle to YugabyteDB’s PostgreSQL-compatible YSQL layer, one of the trickiest differences is exception handling. Oracle has its own way of raising and catching custom errors, while PostgreSQL/PLpgSQL uses RAISE with SQLSTATE codes.

In this tip, we’ll walk through the differences, provide side-by-side examples, and suggest a consistent mapping strategy for custom error codes.

Raising Exceptions

Oracle

In Oracle, you use RAISE_APPLICATION_ERROR with a negative number in the range -20000 to -20999 (or other numeric codes in some cases):

				
					RAISE_APPLICATION_ERROR(-50001, 'Custom exception: cannot drop partition');

YugabyteDB / PostgreSQL

In PL/pgSQL, you raise exceptions using RAISE EXCEPTION. Instead of an arbitrary number, you must supply a valid SQLSTATE code (five characters).

				
					RAISE EXCEPTION 'Custom exception: cannot drop partition'
  USING ERRCODE = 'P0001';

Catching Exceptions

Oracle

Oracle lets you trap specific numeric codes:

				
					EXCEPTION
  WHEN OTHERS THEN
    -- fallback
  WHEN -50001 THEN
    -- handle specific case

YugabyteDB / PostgreSQL

In PL/pgSQL, you catch by SQLSTATE, not by number:

				
					EXCEPTION
  WHEN SQLSTATE 'P0001' THEN
    -- handle specific case
  WHEN OTHERS THEN
    -- fallback

Logging vs. Printing Messages

Oracle

				
					DBMS_OUTPUT.put_line('email');

YugabyteDB / PostgreSQL

				
					RAISE NOTICE 'email';

You can also use RAISE INFO, RAISE WARNING, or RAISE DEBUG for different severity levels.

💡 Pro Tip: You can install the orafce PostgreSQL extension in YugabyteDB, which provides Oracle-style functions, including DBMS_OUTPUT.put_line!

Mapping Strategy: Oracle → YugabyteDB

To keep migrations consistent, you can define a mapping between Oracle’s custom negative codes and PostgreSQL’s Pxxxx codes:

Example: Side-by-Side

Oracle

				
					BEGIN
  IF l_cnt = 0 THEN
    RAISE_APPLICATION_ERROR(-50001, 'No partitions found');
  END IF;
EXCEPTION
  WHEN -50001 THEN
    DBMS_OUTPUT.put_line('Partition cleanup skipped');
END;

YugabyteDB / PostgreSQL

				
					DO $$
DECLARE
  l_cnt int := 0;
BEGIN
  IF l_cnt = 0 THEN
    RAISE EXCEPTION 'No partitions found'
      USING ERRCODE = 'P0001';
  END IF;
EXCEPTION
  WHEN SQLSTATE 'P0001' THEN
    RAISE NOTICE 'Partition cleanup skipped';
END$$;

Summary

● Oracle: uses RAISE_APPLICATION_ERROR(-nnnnn, message) and catches by number.
● YugabyteDB/Postgres: uses RAISE EXCEPTION 'msg' USING ERRCODE = 'Pxxxx' and catches by SQLSTATE.
● Migration tip: Define a consistent mapping (e.g., Oracle -50001 → P0001) and apply it everywhere in your migrated code.
● Logging: Replace DBMS_OUTPUT.put_line with RAISE NOTICE or similar.

👉 If you hit an Oracle exception block during migration, don’t panic. With a consistent mapping and a few syntax tweaks, you can bring your exception handling into YugabyteDB cleanly.

Have Fun!