PL/pgSQL was modeled on PL/SQL, so a large share of Oracle code converts with little syntax change. The rework comes from the parts that convert and then behave differently: missing rows, exceptions, transactions and package state. Those decide the timeline.

The mechanical differences

  • NVL(a, b) becomes COALESCE(a, b). DECODE becomes CASE.
  • SYSDATE becomes clock_timestamp() when the code expects time to advance inside a transaction, or now() when it does not.
  • ROWNUM becomes LIMIT or row_number().
  • CONNECT BY becomes WITH RECURSIVE.
  • The (+) outer join becomes an ANSI LEFT JOIN.
  • MINUS becomes EXCEPT.
  • FROM DUAL is removed.
  • seq.NEXTVAL becomes nextval('seq').
  • RAISE_APPLICATION_ERROR becomes RAISE EXCEPTION.
  • DBMS_OUTPUT.PUT_LINE becomes RAISE NOTICE.
  • Triggers move their body into a trigger function. :NEW and :OLD become NEW and OLD.

One procedure, both sides

-- PL/SQL
CREATE OR REPLACE PROCEDURE close_order(p_order_id IN NUMBER) AS
  v_status orders.status%TYPE;
BEGIN
  SELECT status INTO v_status FROM orders WHERE order_id = p_order_id;
  IF v_status <> 'open' THEN
    RAISE_APPLICATION_ERROR(-20001, 'Order not open');
  END IF;
  UPDATE orders SET status = 'closed', closed_at = SYSDATE
  WHERE  order_id = p_order_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20002, 'Order not found');
END;
-- PL/pgSQL
CREATE PROCEDURE close_order(p_order_id bigint)
LANGUAGE plpgsql AS $$
DECLARE
  v_status orders.status%TYPE;
BEGIN
  SELECT status INTO STRICT v_status FROM orders WHERE order_id = p_order_id;
  IF v_status <> 'open' THEN
    RAISE EXCEPTION 'Order not open' USING ERRCODE = 'P0001';
  END IF;
  UPDATE orders SET status = 'closed', closed_at = now()
  WHERE  order_id = p_order_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE EXCEPTION 'Order not found' USING ERRCODE = 'P0002';
END $$;

The one word that matters is STRICT. Without it, PL/pgSQL does not raise NO_DATA_FOUND when the query returns no row. The variable is set to NULL and the procedure continues. A converter that drops that detail produces code that compiles, passes a happy-path test, and closes orders that do not exist.

The differences that cause rework

  • Missing and multiple rows. SELECT INTO raises NO_DATA_FOUND and TOO_MANY_ROWS only with STRICT. Every SELECT INTO in the estate needs that decision.
  • Packages. PostgreSQL has no packages. The usual target is one schema per package. Package variables that hold session state have no direct equivalent and need a design: session settings, temporary tables, or passing state explicitly.
  • Autonomous transactions. PRAGMA AUTONOMOUS_TRANSACTION does not exist. It is most often used for audit logging that must survive a rollback. Replacements use a second connection or move the logging out of the database transaction. Each use needs review.
  • Commits inside code. Oracle procedures commit freely. PostgreSQL procedures can commit only when called outside an explicit transaction, never inside a block with an exception handler, and functions cannot commit at all. Batch jobs that commit every N rows need a deliberate design.
  • Exception blocks roll back. When a PL/pgSQL block with an EXCEPTION clause catches an error, everything that block did is rolled back. In Oracle, earlier statements in the block stay in place. Handlers that log and continue can produce different data.
  • Empty strings. Oracle treats '' as NULL. Logic built on IS NULL checks for text behaves differently on PostgreSQL.
  • Built-in packages. DBMS_SCHEDULER, UTL_FILE, UTL_HTTP, DBMS_LOB and Advanced Queuing have no built-in counterparts. Scheduling moves to pg_cron or an external scheduler. The orafce extension supplies some compatibility functions. The rest moves to the application.
  • Bulk operations. BULK COLLECT and FORALL usually become a single set-based statement, which is simpler and often faster.

How to size the work

  • Count packages, procedures, functions and triggers. Then find the ones that are called. Unused code is common and should not be converted.
  • Classify each unit: mechanical, needs redesign, or move to the application.
  • Flag every use of package state, autonomous transactions, commits inside loops, and built-in packages with no counterpart.
  • Run a conversion tool for its report, not only its output. The count of items it could not convert is a useful first estimate.
  • Write the test before the conversion. Same inputs, same outputs, on both platforms, including the no-row and error paths.

The number of units in the redesign class is the best single predictor of the migration timeline. Get that number before agreeing a cutover date or an Oracle license end date.

Where this leads

Datrick operates Oracle and PostgreSQL in production and delivers Oracle to PostgreSQL migration under a written scope, with named engineers accountable for assessment, conversion, validation, cutover and the operation that follows. The service page describes the delivery model: Oracle to PostgreSQL migration. For a first read of risk and effort, see the migration risk assessment.

Related: Oracle to PostgreSQL migration · migration risk assessment · Oracle DBA services · PostgreSQL DBA services · T-SQL to PL/pgSQL conversion