Packages

A package groups related procedures, functions, types, cursors, and variables into a single named unit. It is Oracle's most powerful PL/SQL construct — enabling encapsulation, overloading, persistent session state, and efficient dependency management.

Package Structure: Spec + Body

Every package has two separate objects:

Package Specification (the public interface — what callers see):

CREATE OR REPLACE PACKAGE emp_mgr AS

    -- Public type
    TYPE t_emp_summary IS RECORD (
        emp_id    employees.employee_id%TYPE,
        full_name VARCHAR2(100),
        salary    employees.salary%TYPE,
        dept_name VARCHAR2(100)
    );

    -- Public constant
    c_max_salary CONSTANT NUMBER := 250000;

    -- Public procedure signatures
    PROCEDURE hire_employee(
        p_first_name    IN  employees.first_name%TYPE,
        p_last_name     IN  employees.last_name%TYPE,
        p_job_id        IN  employees.job_id%TYPE,
        p_salary        IN  employees.salary%TYPE,
        p_department_id IN  employees.department_id%TYPE,
        p_new_emp_id    OUT employees.employee_id%TYPE
    );

    PROCEDURE terminate_employee(
        p_emp_id    IN employees.employee_id%TYPE,
        p_end_date  IN DATE DEFAULT SYSDATE
    );

    -- Public function signatures
    FUNCTION get_summary(p_emp_id IN NUMBER) RETURN t_emp_summary;
    FUNCTION annual_salary(p_emp_id IN NUMBER) RETURN NUMBER;

END emp_mgr;
/

Package Body (the private implementation):

CREATE OR REPLACE PACKAGE BODY emp_mgr AS

    -- Private variable (session-level state)
    g_last_emp_id  employees.employee_id%TYPE;

    -- Private helper procedure (not in spec — callers cannot call this)
    PROCEDURE validate_salary(p_salary IN NUMBER, p_job_id IN VARCHAR2) IS
        v_min  jobs.min_salary%TYPE;
        v_max  jobs.max_salary%TYPE;
    BEGIN
        SELECT min_salary, max_salary
        INTO   v_min, v_max
        FROM   jobs
        WHERE  job_id = p_job_id;

        IF p_salary < v_min OR p_salary > v_max THEN
            RAISE_APPLICATION_ERROR(-20010,
                'Salary $' || p_salary ||
                ' outside range for ' || p_job_id ||
                ' ($' || v_min || '–$' || v_max || ')');
        END IF;
    END validate_salary;

    -- Public procedure implementation
    PROCEDURE hire_employee(
        p_first_name    IN  employees.first_name%TYPE,
        p_last_name     IN  employees.last_name%TYPE,
        p_job_id        IN  employees.job_id%TYPE,
        p_salary        IN  employees.salary%TYPE,
        p_department_id IN  employees.department_id%TYPE,
        p_new_emp_id    OUT employees.employee_id%TYPE
    ) IS
    BEGIN
        validate_salary(p_salary, p_job_id);  -- private call

        INSERT INTO employees (
            employee_id, first_name, last_name, email,
            hire_date, job_id, salary, department_id
        ) VALUES (
            employees_seq.NEXTVAL,
            p_first_name, p_last_name,
            UPPER(SUBSTR(p_first_name, 1, 1) || p_last_name),
            SYSDATE,
            p_job_id, p_salary, p_department_id
        )
        RETURNING employee_id INTO p_new_emp_id;

        g_last_emp_id := p_new_emp_id;  -- update package state
        COMMIT;
    END hire_employee;

    PROCEDURE terminate_employee(
        p_emp_id   IN employees.employee_id%TYPE,
        p_end_date IN DATE DEFAULT SYSDATE
    ) IS
    BEGIN
        INSERT INTO job_history (
            employee_id, start_date, end_date, job_id, department_id
        )
        SELECT employee_id, hire_date, p_end_date, job_id, department_id
        FROM   employees
        WHERE  employee_id = p_emp_id;

        DELETE FROM employees WHERE employee_id = p_emp_id;

        IF SQL%NOTFOUND THEN
            RAISE_APPLICATION_ERROR(-20011, 'Employee ' || p_emp_id || ' not found');
        END IF;
        COMMIT;
    END terminate_employee;

    FUNCTION get_summary(p_emp_id IN NUMBER) RETURN t_emp_summary IS
        v_rec  t_emp_summary;
    BEGIN
        SELECT e.employee_id,
               e.first_name || ' ' || e.last_name,
               e.salary,
               d.department_name
        INTO   v_rec
        FROM   employees   e
        JOIN   departments d ON d.department_id = e.department_id
        WHERE  e.employee_id = p_emp_id;

        RETURN v_rec;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20012, 'Employee ' || p_emp_id || ' not found');
    END get_summary;

    FUNCTION annual_salary(p_emp_id IN NUMBER) RETURN NUMBER IS
        v_sal  employees.salary%TYPE;
    BEGIN
        SELECT salary INTO v_sal FROM employees WHERE employee_id = p_emp_id;
        RETURN v_sal * 12;
    END annual_salary;

END emp_mgr;
/

Calling Package Members

DECLARE
    v_new_id  employees.employee_id%TYPE;
    v_summary emp_mgr.t_emp_summary;
BEGIN
    -- Call public procedure
    emp_mgr.hire_employee(
        p_first_name    => 'Alice',
        p_last_name     => 'Chen',
        p_job_id        => 'IT_PROG',
        p_salary        => 7500,
        p_department_id => 60,
        p_new_emp_id    => v_new_id
    );
    DBMS_OUTPUT.PUT_LINE('New employee ID: ' || v_new_id);

    -- Call public function
    v_summary := emp_mgr.get_summary(v_new_id);
    DBMS_OUTPUT.PUT_LINE(v_summary.full_name || ' — $' || v_summary.salary);

    -- Use public constant
    IF v_summary.salary > emp_mgr.c_max_salary THEN
        DBMS_OUTPUT.PUT_LINE('Salary exceeds cap!');
    END IF;
END;
/

Package State (Session-Level Globals)

Package variables persist for the lifetime of a session:

CREATE OR REPLACE PACKAGE session_ctx AS
    g_user_id      NUMBER;
    g_app_name     VARCHAR2(50) := 'SQLMentor';
    g_debug_mode   BOOLEAN      := FALSE;
    PROCEDURE init(p_user_id IN NUMBER);
END session_ctx;
/

CREATE OR REPLACE PACKAGE BODY session_ctx AS
    PROCEDURE init(p_user_id IN NUMBER) IS
    BEGIN
        g_user_id := p_user_id;
        DBMS_OUTPUT.PUT_LINE('Session initialised for user ' || p_user_id);
    END init;
END session_ctx;
/

-- First call initialises the session
BEGIN session_ctx.init(42); END;
/

-- Later in the same session, g_user_id is still 42
BEGIN
    DBMS_OUTPUT.PUT_LINE('User: ' || session_ctx.g_user_id);
END;
/

Overloading

The same subprogram name with different parameter signatures:

CREATE OR REPLACE PACKAGE formatter AS
    FUNCTION format_amount(p_val IN NUMBER)   RETURN VARCHAR2;
    FUNCTION format_amount(p_val IN DATE)     RETURN VARCHAR2;
    FUNCTION format_amount(p_val IN VARCHAR2) RETURN VARCHAR2;
END formatter;
/

CREATE OR REPLACE PACKAGE BODY formatter AS
    FUNCTION format_amount(p_val IN NUMBER) RETURN VARCHAR2 IS
    BEGIN RETURN '$' || TO_CHAR(p_val, 'FM999,999.00'); END;

    FUNCTION format_amount(p_val IN DATE) RETURN VARCHAR2 IS
    BEGIN RETURN TO_CHAR(p_val, 'DD-MON-YYYY'); END;

    FUNCTION format_amount(p_val IN VARCHAR2) RETURN VARCHAR2 IS
    BEGIN RETURN '''' || TRIM(p_val) || ''''; END;
END formatter;
/

Forward Declarations

When procedure A calls procedure B, and B is defined after A, use a forward declaration:

CREATE OR REPLACE PACKAGE BODY demo AS
    PROCEDURE proc_b;  -- forward declaration

    PROCEDURE proc_a IS
    BEGIN
        proc_b;  -- legal because of forward declaration above
    END proc_a;

    PROCEDURE proc_b IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('proc_b called');
    END proc_b;
END demo;
/

Why Packages Beat Standalone Subprograms

The STANDARD Package

Oracle's built-in STANDARD package defines all PL/SQL built-ins: VARCHAR2, NUMBER, SYSDATE, NVL, TO_CHAR, etc. It is always in scope — you never need to prefix it. This is why VARCHAR2 works without STANDARD.VARCHAR2.

Summary

• A package has a spec (public interface) and a body (implementation).
• Members not in the spec are private — inaccessible to callers.
• Package variables are session-level — they persist between calls within one session.
• The same name with different parameter types is overloading — only possible in packages.
• Changing the body without touching the spec does not invalidate dependent objects.
• Use packages to organize, encapsulate, and share related PL/SQL logic.