Oracle to Azure Database for PostgreSQL Migration¶

-- Oracle PL/SQL
CREATE OR REPLACE FUNCTION get_employee_salary(
    p_emp_id IN NUMBER
) RETURN NUMBER IS
    v_salary NUMBER(10,2);
BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = p_emp_id;

    RETURN v_salary;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
    WHEN TOO_MANY_ROWS THEN
        RAISE_APPLICATION_ERROR(-20001, 'Multiple employees found');
END get_employee_salary;
/

-- PL/pgSQL (Azure PostgreSQL)
CREATE OR REPLACE FUNCTION get_employee_salary(
    p_emp_id integer
) RETURNS numeric AS $$
DECLARE
    v_salary numeric(10,2);
BEGIN
    SELECT salary INTO STRICT v_salary
    FROM employees
    WHERE employee_id = p_emp_id;

    RETURN v_salary;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
    WHEN TOO_MANY_ROWS THEN
        RAISE EXCEPTION 'Multiple employees found' USING ERRCODE = 'P0001';
END;
$$ LANGUAGE plpgsql;

-- Oracle package spec
CREATE OR REPLACE PACKAGE hr_pkg AS
    c_max_salary CONSTANT NUMBER := 500000;

    FUNCTION validate_salary(p_salary NUMBER) RETURN BOOLEAN;
    PROCEDURE give_raise(p_emp_id NUMBER, p_pct NUMBER);
END hr_pkg;
/

CREATE OR REPLACE PACKAGE BODY hr_pkg AS
    FUNCTION validate_salary(p_salary NUMBER) RETURN BOOLEAN IS
    BEGIN
        RETURN p_salary > 0 AND p_salary <= c_max_salary;
    END;

    PROCEDURE give_raise(p_emp_id NUMBER, p_pct NUMBER) IS
        v_new_salary NUMBER;
    BEGIN
        SELECT salary * (1 + p_pct/100) INTO v_new_salary
        FROM employees WHERE employee_id = p_emp_id;

        IF NOT validate_salary(v_new_salary) THEN
            RAISE_APPLICATION_ERROR(-20002, 'Salary exceeds maximum');
        END IF;

        UPDATE employees SET salary = v_new_salary
        WHERE employee_id = p_emp_id;
    END;
END hr_pkg;
/

-- PL/pgSQL equivalent
CREATE SCHEMA IF NOT EXISTS hr_pkg;

-- Package constants become a configuration table or function
CREATE OR REPLACE FUNCTION hr_pkg.max_salary()
RETURNS numeric AS $$
BEGIN
    RETURN 500000;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION hr_pkg.validate_salary(p_salary numeric)
RETURNS boolean AS $$
BEGIN
    RETURN p_salary > 0 AND p_salary <= hr_pkg.max_salary();
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE hr_pkg.give_raise(
    p_emp_id integer,
    p_pct numeric
) AS $$
DECLARE
    v_new_salary numeric;
BEGIN
    SELECT salary * (1 + p_pct/100.0) INTO v_new_salary
    FROM employees WHERE employee_id = p_emp_id;

    IF NOT hr_pkg.validate_salary(v_new_salary) THEN
        RAISE EXCEPTION 'Salary exceeds maximum' USING ERRCODE = 'P0002';
    END IF;

    UPDATE employees SET salary = v_new_salary
    WHERE employee_id = p_emp_id;
END;
$$ LANGUAGE plpgsql;

-- Oracle: Nested table type
CREATE OR REPLACE TYPE number_list AS TABLE OF NUMBER;
/

CREATE OR REPLACE FUNCTION sum_numbers(p_numbers number_list)
RETURN NUMBER IS
    v_sum NUMBER := 0;
BEGIN
    FOR i IN 1..p_numbers.COUNT LOOP
        v_sum := v_sum + p_numbers(i);
    END LOOP;
    RETURN v_sum;
END;
/

-- PL/pgSQL: Use arrays
CREATE OR REPLACE FUNCTION sum_numbers(p_numbers numeric[])
RETURNS numeric AS $$
DECLARE
    v_sum numeric := 0;
    v_num numeric;
BEGIN
    FOREACH v_num IN ARRAY p_numbers LOOP
        v_sum := v_sum + v_num;
    END LOOP;
    RETURN v_sum;
END;
$$ LANGUAGE plpgsql;

-- Or simpler with built-in aggregate:
-- SELECT sum(unnest) FROM unnest(p_numbers);

-- Enable extensions (Azure PostgreSQL Flexible Server)
-- Some extensions are pre-installed, others need allow-listing in Azure Portal

-- Check available extensions
SELECT * FROM pg_available_extensions ORDER BY name;

-- Enable PostGIS
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;

-- Enable pgvector for AI/RAG patterns
CREATE EXTENSION IF NOT EXISTS vector;

-- Enable pg_cron for scheduling
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Enable pgAudit for audit logging
-- (Must be enabled in server parameters first via Azure Portal)
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- Enable Citus for distributed tables
-- (Available on Citus-enabled server configuration)
CREATE EXTENSION IF NOT EXISTS citus;

-- Distribute a high-volume table across workers
SELECT create_distributed_table('transactions', 'tenant_id');

-- Reference tables (small lookup tables replicated to all workers)
SELECT create_reference_table('transaction_types');
SELECT create_reference_table('currencies');

-- Queries run in parallel across all workers
SELECT tenant_id, COUNT(*), SUM(amount)
FROM transactions
WHERE transaction_date >= '2024-01-01'
GROUP BY tenant_id;
-- Citus automatically parallelizes and aggregates

-- PostgreSQL: Validate row counts against Oracle source
SELECT schemaname, relname AS table_name,
       n_live_tup AS approximate_row_count
FROM pg_stat_user_tables
WHERE schemaname = 'app_schema'
ORDER BY relname;

-- Validate data types were correctly mapped
SELECT column_name, data_type, character_maximum_length,
       numeric_precision, numeric_scale
FROM information_schema.columns
WHERE table_schema = 'app_schema' AND table_name = 'employees'
ORDER BY ordinal_position;

-- Performance baseline
SELECT query, calls, mean_exec_time, total_exec_time,
       rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;