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)becomesCOALESCE(a, b).DECODEbecomesCASE.SYSDATEbecomesclock_timestamp()when the code expects time to advance inside a transaction, ornow()when it does not.ROWNUMbecomesLIMITorrow_number().CONNECT BYbecomesWITH RECURSIVE.- The
(+)outer join becomes an ANSILEFT JOIN. MINUSbecomesEXCEPT.FROM DUALis removed.seq.NEXTVALbecomesnextval('seq').RAISE_APPLICATION_ERRORbecomesRAISE EXCEPTION.DBMS_OUTPUT.PUT_LINEbecomesRAISE NOTICE.- Triggers move their body into a trigger function.
:NEWand:OLDbecomeNEWandOLD.
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 INTOraisesNO_DATA_FOUNDandTOO_MANY_ROWSonly withSTRICT. EverySELECT INTOin 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_TRANSACTIONdoes 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
EXCEPTIONclause 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 onIS NULLchecks for text behaves differently on PostgreSQL. - Built-in packages.
DBMS_SCHEDULER,UTL_FILE,UTL_HTTP,DBMS_LOBand Advanced Queuing have no built-in counterparts. Scheduling moves topg_cronor an external scheduler. Theorafceextension supplies some compatibility functions. The rest moves to the application. - Bulk operations.
BULK COLLECTandFORALLusually 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
