Oracle to AlloyDB | Cloud SQL for PostgreSQL Migration Series: A Developer’s Guide | by Zain Muhammad | Google Cloud – Community | Jun, 2025

A deep dive into the foundational shift from Oracle Packages to PostgreSQL Schemas.

If you’re a developer migrating from Oracle, you know that the PACKAGE is more than just a feature—it’s the cornerstone of your application architecture. Packages are the logical toolboxes where you organize related procedures, functions, types, and crucially, session-level variables. So, one of the first and most significant discoveries when moving to a PostgreSQL-compatible database—whether it’s AlloyDB for PostgreSQL, Cloud SQL for PostgreSQL, or a self-managed instance—is a simple, stark fact: PostgreSQL doesn’t have packages.

This isn’t a minor syntax change; it’s a fundamental architectural shift. Attempting a direct, one-to-one conversion will fail. Understanding why and learning the correct pattern is the first critical step toward a successful migration.

The Core of the Problem: Container vs. Namespace

In Oracle, a package acts as a container. It encapsulates logic and state. You can have public and private subprograms, and its variables maintain their state for the entire duration of a database session.

PostgreSQL and its compatible flavors, on the other hand, use schemas for organization. A schema is more like a namespace or a directory. It groups objects like tables and functions under a common name to prevent name collisions, but it provides neither the encapsulation nor the session-state management of an Oracle Package.

So, how do you bridge this gap across the Postgres ecosystem?

The Recommended Strategy: Map Packages to Schemas

The most pragmatic and maintainable approach is a one-to-one mapping where each Oracle Package becomes a distinct PostgreSQL Schema.

If your Oracle code calls my_reporting_package.generate_report(), your PostgreSQL code will call my_reporting_package.generate_report()—the syntax looks comfortingly familiar, making the migration path much clearer.

• Pros: It’s clean, preserves the logical separation of your code, and makes object references easy to manage.
• Cons: You must watch for potential naming conflicts. If you had a utils schema and a utils package in Oracle, you’ll need a clear naming strategy to avoid a collision in Postgres.

Despite the minor risk of naming conflicts (which can be managed), this approach is far superior to the alternative.

(The Alternative to Avoid: Some consider consolidating all packages into a single schema and prefixing object names (e.g., my_reporting_package_generate_report()). This is generally a bad idea. It leads to incredibly long object names that can exceed PostgreSQL’s 63-byte limit and requires a massive, high-risk refactoring effort.)

The Follow-Up Problem: Replicating Package Variables

Solving the package-to-schema mapping reveals the next challenge: what about those session-level variables that Oracle packages handle so gracefully? Since a Postgres schema doesn’t hold state, we need another universal pattern that works across AlloyDB, Cloud SQL, and all Postgres instances.

The solution is to emulate this behavior using a session-level temporary table.

Here’s the pattern:

1. A Custom Type: Define a type that holds the structure of all your “package” variables.
2. A Temporary Table: Create a temporary table with a single row of that custom type. This table exists only for the duration of the current session.
3. Getter/Setter Functions: Create functions to initialize, read (get), and update (set) the values in this temporary table.

Let’s say you’re converting a coredata package with a variable named related_item. The PostgreSQL implementation would look like this:

-- 1. Define the type to hold our session variables
CREATE TYPE coredata.global_var_type AS (
related_item item
);

---
-- 2. Create helper functions to manage a temporary table
----- Initializes the session-level temp table
CREATE OR REPLACE FUNCTION coredata.init_global_var()
RETURNS void LANGUAGE 'plpgsql' AS $$
BEGIN
-- This table is temporary and only visible to the current session
CREATE TEMPORARY TABLE IF NOT EXISTS CORE_GLOBAL_VAR (
GLOBAL_VAR_VAL coredata.global_var_type
);
-- Ensure there's a row to update
IF NOT EXISTS (SELECT 1 FROM CORE_GLOBAL_VAR) THEN
INSERT INTO CORE_GLOBAL_VAR VALUES (ROW(NULL));
END IF;
END;
$$;----- Gets the current values
CREATE OR REPLACE FUNCTION coredata.get_global_var()
RETURNS coredata.global_var_type LANGUAGE 'plpgsql' AS $$
DECLARE
v_global_var coredata.global_var_type;
BEGIN
-- Ensure the temp table is initialized
PERFORM coredata.init_global_var();
-- Retrieve the values
SELECT GLOBAL_VAR_VAL INTO v_global_var FROM CORE_GLOBAL_VAR LIMIT 1;
RETURN v_global_var;
END;
$$;----- Sets new values
CREATE OR REPLACE PROCEDURE coredata.set_global_var(
IN p_global_var_input coredata.global_var_type
)
LANGUAGE 'plpgsql' AS $$
BEGIN
-- Ensure the temp table is initialized
PERFORM coredata.init_global_var();
-- Update the single row with the new values
UPDATE CORE_GLOBAL_VAR SET GLOBAL_VAR_VAL = p_global_var_input;
END;
$$;

The Takeaway

Migrating from Oracle Packages isn’t about finding a direct replacement; it’s about embracing a new way of thinking that applies across the entire PostgreSQL ecosystem. By mapping packages to schemas and using the temporary table pattern for session state, you can successfully replicate the functionality you need in a way that is robust, scalable, and idiomatic to PostgreSQL, AlloyDB, and Cloud SQL for PostgreSQL.

Next up in our series: “Mind the Gotchas: A Developer’s Guide to most Critical Differences That Can Break Your Oracle to AlloyDB | CloudSQL (PostgreSQL) Migration”