Migrating Oracle packages to PostgreSQL

Why a straight translation is not enough

An Oracle package specification often acts as a contract with the application. The body contains the implementation, internal helper routines and sometimes session state that survives across calls. Translating individual functions can therefore change behaviour even when the SQL appears equivalent.

PostgreSQL has no direct equivalent of an Oracle package with the same semantics. Schemas can organise functions and procedures, but they do not automatically create private package members or package variables. Both aspects require an explicit target design.

Inventory the interface and the state

• List public routines, parameters, data types and exceptions. Which calls are actually used by the application?
• Identify package variables and initialisation logic. Is state held per session, transaction or business process?
• Capture internal helper routines, grants and calls across schema boundaries.
• Review dynamic SQL, transaction control and external side effects separately.

Lines of code are not a useful risk metric on their own. A small package with session state and many callers may be more difficult to migrate than a large package of independent calculations.

Example: isolate a stateless calculation

The following example deliberately isolates one calculation. It is not a complete package-migration pattern. In a test database where schema creation is permitted, the target routine can be validated without involving the application or production data.

CREATE SCHEMA IF NOT EXISTS migration_demo;

CREATE OR REPLACE FUNCTION migration_demo.gross_amount(
  p_net_amount numeric,
  p_tax_rate numeric
) RETURNS numeric
LANGUAGE sql
IMMUTABLE
RETURNS NULL ON NULL INPUT
AS $$
  SELECT round(p_net_amount * (1 + p_tax_rate), 2);
$$;

SELECT migration_demo.gross_amount(100, 0.19);
-- Expected value: 119.00

SELECT migration_demo.gross_amount(NULL, 0.19);
-- Expected value: NULL

NULL behaviour and rounding are explicit here. In a real migration those choices must match the existing business behaviour. A default parameter value or a different rounding rule would already be an interface change.

Model package state explicitly

Passing context explicitly can make dependencies visible. A session table may be appropriate for some cases, but it ties behaviour to the database connection. Durable business state may belong in a regular data model or in the application instead.

Visibility of internal routines is also an authorisation decision. Use appropriate roles and verify actual execution privileges. A different name or schema does not make a function private by itself.

Turn the package into a testable migration unit

• Choose one business process and identify every caller involved.
• Compare old and new behaviour with identical inputs: normal cases, NULLs, boundary values, errors and transactions.
• Test repeated calls, connection changes and concurrent execution.
• Verify privileges using the real application role.
• Only after the pilot should assumptions and effort estimates be extrapolated to similar packages.

The deliverable should map each Oracle routine to a target routine, a test case and an owner. That turns a conversion list into an executable migration package.