PL/SQL to PL/pgSQL Conversion: Patterns With Code Examples

The short answer

PL/pgSQL is block-structured like PL/SQL, so most routines convert line by line once you know about a dozen patterns. The mechanical ones: RETURN becomes RETURNS in the header, IS becomes AS, the body goes inside dollar quotes, and a LANGUAGE plpgsql clause closes it. The ones that change behaviour, and so need tests: SELECT INTO no longer raises an error on zero rows unless you add STRICT, an exception handler rolls back everything since its block began, packages and their variables do not exist, REVERSE loops take their bounds the other way round, and autonomous transactions need a second session.

ora2pg converts headers and parameters automatically and its documentation is frank that you will still have manual work. Use the patterns below to review what it produced. The migration challenges post covers the data-level traps such as empty strings and DATE, and the step-by-step guide places code conversion in the wider project.

1. Function headers

This is the PostgreSQL documentation's own porting example. Change varchar2 to varchar or text, RETURN to RETURNS, IS to AS, wrap the body in $$, and drop the trailing slash and show errors.

-- Oracle
CREATE OR REPLACE FUNCTION fmt_version(v_name varchar2, v_version varchar2)
RETURN varchar2 IS
BEGIN
  IF v_version IS NULL THEN
    RETURN v_name;
  END IF;
  RETURN v_name || '/' || v_version;
END;
/

-- PostgreSQL
CREATE OR REPLACE FUNCTION fmt_version(v_name text, v_version text)
RETURNS text AS $$
BEGIN
  IF v_version IS NULL THEN
    RETURN v_name;
  END IF;
  RETURN v_name || '/' || v_version;
END;
$$ LANGUAGE plpgsql;

Two optional additions pay off: STRICT when the function should return NULL for any NULL argument, and a volatility label such as IMMUTABLE or STABLE so the planner can optimise calls. Watch the empty-string rule here too: in Oracle, a caller passing '' as the version hits the IS NULL branch, and in PostgreSQL it does not.

2. Procedures and transaction control

PostgreSQL has had CREATE PROCEDURE since version 11. Call procedures with CALL. A procedure invoked by CALL at the top level can COMMIT and ROLLBACK, so batch procedures that commit every few thousand rows keep their shape. A function cannot commit, and neither can a procedure called from inside a SELECT.

-- Oracle
CREATE OR REPLACE PROCEDURE archive_orders(p_before IN DATE) IS
BEGIN
  INSERT INTO orders_archive SELECT * FROM orders WHERE placed_at < p_before;
  DELETE FROM orders WHERE placed_at < p_before;
  COMMIT;
END;
/

-- PostgreSQL
CREATE OR REPLACE PROCEDURE archive_orders(p_before timestamp)
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO orders_archive SELECT * FROM orders WHERE placed_at < p_before;
  DELETE FROM orders WHERE placed_at < p_before;
  COMMIT;
END;
$$;

CALL archive_orders(localtimestamp(0) - interval '1 year');

PL/pgSQL has no SAVEPOINT statement. Use a nested block with an exception handler, which behaves like a savepoint (see pattern 5).

3. Packages and package state

PostgreSQL has no packages. Put each package's routines in a schema of the same name, which is ora2pg's default, so billing.post_invoice() calls keep working. Package variables are the harder part. For simple session values, use a custom setting with a two-part name. For larger state, use a temporary table, as the PostgreSQL porting guide suggests.

-- Oracle: a package variable
-- CREATE PACKAGE billing AS g_batch_id NUMBER; END;
-- billing.g_batch_id := p_batch_id;

-- PostgreSQL: a session setting in place of the variable
CREATE SCHEMA billing;

CREATE OR REPLACE FUNCTION billing.set_batch(p_batch_id bigint) RETURNS void AS $$
BEGIN
  PERFORM set_config('billing.batch_id', p_batch_id::text, false);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION billing.current_batch() RETURNS bigint AS $$
BEGIN
  RETURN nullif(current_setting('billing.batch_id', true), '')::bigint;
END;
$$ LANGUAGE plpgsql;

A package's initialisation block, which Oracle runs on first use in a session, has no equivalent. Call an initialisation function explicitly, or have the getter initialise the value when it finds none. Private package routines become ordinary functions in the schema; restrict them with REVOKE EXECUTE if callers outside the package must not use them.

4. SELECT INTO, %TYPE and %ROWTYPE

%TYPE and %ROWTYPE work the same way in PL/pgSQL. SELECT INTO does not. In Oracle, a SELECT INTO that finds no row raises NO_DATA_FOUND and one that finds two raises TOO_MANY_ROWS. In PL/pgSQL, without STRICT, it sets the variable to NULL on no rows and takes the first row when there are several. Add STRICT to keep Oracle's behaviour:

DECLARE
  v_limit customers.credit_limit%TYPE;
BEGIN
  SELECT credit_limit INTO STRICT v_limit FROM customers WHERE id = p_id;
EXCEPTION
  WHEN no_data_found THEN
    RAISE EXCEPTION 'Customer % not found', p_id;
END;

Search converted code for every INTO without STRICT whose Oracle original relied on the exception. It is an easy change to miss, because nothing fails.

5. Exceptions and raise_application_error

Exception blocks look the same, with different names. DUP_VAL_ON_INDEX becomes unique_violation, ZERO_DIVIDE becomes division_by_zero, and NO_DATA_FOUND, TOO_MANY_ROWS and OTHERS keep their names. SQLERRM and SQLSTATE are available inside a handler. raise_application_error becomes RAISE EXCEPTION, and USING ERRCODE sets a five-character SQLSTATE if callers check codes rather than messages. Applications that parse Oracle error numbers such as -20001 need changing to read the SQLSTATE.

-- Oracle
BEGIN
  INSERT INTO jobs (job_id, started_at) VALUES (p_job_id, SYSDATE);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN NULL;
END;

-- PostgreSQL
BEGIN
  INSERT INTO jobs (job_id, started_at) VALUES (p_job_id, localtimestamp(0));
EXCEPTION
  WHEN unique_violation THEN NULL;
END;

The behaviour change: when PL/pgSQL catches an exception, it rolls back every change made since that block's BEGIN. The PostgreSQL docs compare it to Oracle code that sets a savepoint at the top of the block and rolls back to it in each handler. If the Oracle code did that, drop the savepoint statements. If it expected earlier statements in the block to survive, move them into an outer block.

6. Loops

Cursor FOR loops convert almost unchanged. Two differences matter. A FOR loop over a query needs its loop variable declared, usually as record, where PL/SQL declares it implicitly. And REVERSE integer loops take their bounds in the opposite order:

-- Oracle: counts 10 down to 1
FOR i IN REVERSE 1..10 LOOP ... END LOOP;

-- PostgreSQL: counts 10 down to 1
FOR i IN REVERSE 10..1 LOOP ... END LOOP;

-- Query loop: declare the target
DECLARE
  r record;
BEGIN
  FOR r IN SELECT id, total FROM orders WHERE status = 'NEW' LOOP
    PERFORM billing.post_invoice(r.id, r.total);
  END LOOP;
END;

SQL%ROWCOUNT becomes GET DIAGNOSTICS v_count = ROW_COUNT, and SQL%FOUND becomes the FOUND variable.

7. Dynamic SQL

EXECUTE IMMEDIATE becomes EXECUTE. Bind values with USING and $1, $2 placeholders in place of :1, and build identifiers with format() and %I so table and column names are quoted safely:

-- Oracle
EXECUTE IMMEDIATE 'DELETE FROM ' || p_table || ' WHERE created_at < :1' USING p_cutoff;

-- PostgreSQL
EXECUTE format('DELETE FROM %I WHERE created_at < $1', p_table) USING p_cutoff;

The PostgreSQL porting guide warns that dynamic statements built by plain concatenation will not work reliably unless names and literals go through quote_ident and quote_literal, or format() with %I and %L.

8. BULK COLLECT and FORALL

PL/pgSQL has no BULK COLLECT or FORALL. Oracle code uses them to cut round trips between the PL/SQL and SQL engines. In PostgreSQL the usual answer is to drop the loop and write one set-based statement. Where the code really needs a collection, use an array:

-- Oracle
SELECT id BULK COLLECT INTO v_ids FROM orders WHERE status = 'NEW';
FORALL i IN 1 .. v_ids.COUNT
  UPDATE orders SET status = 'QUEUED' WHERE id = v_ids(i);

-- PostgreSQL: one statement
UPDATE orders SET status = 'QUEUED' WHERE status = 'NEW';

-- PostgreSQL: keep the collection when later steps need it
SELECT array_agg(id) INTO v_ids FROM orders WHERE status = 'NEW';
UPDATE orders SET status = 'QUEUED' WHERE id = ANY (v_ids);

9. DBMS_OUTPUT and other built-in packages

DBMS_OUTPUT.PUT_LINE is usually debugging, and RAISE NOTICE 'Processed % rows', v_count; replaces it. Where code depends on reading the output buffer back, the orafce extension implements dbms_output, along with utl_file, dbms_pipe, dbms_alert, dbms_random and others. Packages outside orafce's list, such as job scheduling or mail, map to separate tools: a scheduler such as cron or the pg_cron extension, and mail sent by the application rather than the database.

10. Triggers

A PostgreSQL trigger is two objects: a function that returns trigger, and a CREATE TRIGGER statement that calls it. :NEW and :OLD become NEW and OLD, and a BEFORE row trigger must RETURN NEW, or the row is skipped.

-- Oracle
CREATE OR REPLACE TRIGGER orders_touch
BEFORE UPDATE ON orders FOR EACH ROW
BEGIN
  :NEW.updated_at := SYSDATE;
END;
/

-- PostgreSQL
CREATE OR REPLACE FUNCTION orders_touch() RETURNS trigger AS $$
BEGIN
  NEW.updated_at := localtimestamp(0);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_touch BEFORE UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION orders_touch();

Triggers that fill keys from a sequence can usually be replaced by an identity column or a column default.

11. Autonomous transactions

There is no PRAGMA AUTONOMOUS_TRANSACTION. The standard workaround runs the statement in a second session through the dblink extension, so it commits even if the caller rolls back. The pg_background extension does the same in a background worker, and ora2pg can generate either wrapper.

CREATE EXTENSION IF NOT EXISTS dblink;

CREATE OR REPLACE FUNCTION app.log_error(p_message text) RETURNS void AS $$
BEGIN
  PERFORM dblink_exec(
    'dbname=' || current_database(),
    format('INSERT INTO app.error_log (logged_at, message) VALUES (now(), %L)', p_message));
END;
$$ LANGUAGE plpgsql;

Each call opens a connection, so the role needs credentials that work without a prompt, and the pattern does not belong in a tight loop.

A review checklist for converted code

• Every SELECT INTO that relied on NO_DATA_FOUND has STRICT.
• Every exception handler has been checked for the implicit rollback.
• Every REVERSE loop has swapped bounds.
• Every IS NULL test on a string has been checked for callers passing ''.
• Every DATE variable that holds a time is a timestamp.
• Every package variable has a replacement and an initialisation path.
• Every dynamic statement quotes identifiers with %I and binds values with USING.

Where OSSeva fits

Oracle-to-Postgres migration design is part of OSSeva Assure, including the query compatibility analysis that finds these patterns in your code before conversion starts. Once converted code is in production, OSSeva for PostgreSQL supports the cluster it runs on, and keeps patching it after its PostgreSQL version reaches community end of life. See Oracle to PostgreSQL with OSSeva.

Frequently asked questions

Can PL/SQL be converted to PL/pgSQL automatically?

Partly. ora2pg and AWS SCT convert headers, parameters, types and common syntax, and flag what they could not convert. Exception behaviour, package state, bulk operations and dynamic SQL still need a person to review them.

What is the PL/pgSQL equivalent of a PL/SQL package?

A schema holding the package's functions and procedures. Package variables become session settings or temporary tables.

Why does my converted SELECT INTO not raise NO_DATA_FOUND?

PL/pgSQL only raises it when the statement says INTO STRICT. Without STRICT, the variable is set to NULL.

How do you replace raise_application_error in PostgreSQL?

With RAISE EXCEPTION 'message', args, adding USING ERRCODE when callers check an error code.

Does PostgreSQL support COMMIT inside a stored procedure?

Yes, from PostgreSQL 11, in procedures invoked with CALL at the top level. Functions cannot commit.