PL/SQL Package Design Patterns for Maintainable Code

Packages Are the Foundation of Good PL/SQL Architecture

Standalone procedures and functions have their place, but packages are the preferred organizational unit for production PL/SQL code. Packages provide encapsulation (hiding implementation details), session state (package-level variables persist across calls within a session), better dependency management (changes to the package body do not invalidate dependent objects), and performance benefits (the entire package is loaded into memory on first reference).

The Table API Pattern

Create a package for each major table or entity that encapsulates all DML operations:

CREATE OR REPLACE PACKAGE emp_api AS
    FUNCTION get_by_id(p_emp_id IN NUMBER) RETURN employees%ROWTYPE;
    FUNCTION exists_yn(p_emp_id IN NUMBER) RETURN VARCHAR2;

    PROCEDURE ins(
        p_first_name IN VARCHAR2,
        p_last_name  IN VARCHAR2,
        p_email      IN VARCHAR2,
        p_dept_id    IN NUMBER,
        p_emp_id     OUT NUMBER
    );

    PROCEDURE upd(
        p_emp_id     IN NUMBER,
        p_first_name IN VARCHAR2 DEFAULT NULL,
        p_last_name  IN VARCHAR2 DEFAULT NULL,
        p_email      IN VARCHAR2 DEFAULT NULL
    );

    PROCEDURE del(p_emp_id IN NUMBER);
END emp_api;
/

This pattern centralizes validation, auditing, and business rules for the entity. Every piece of code that modifies employees goes through emp_api, ensuring consistent behavior.

The Service Layer Pattern

Above table APIs, create service packages that implement business workflows spanning multiple tables:

CREATE OR REPLACE PACKAGE order_service AS
    PROCEDURE submit_order(
        p_customer_id IN NUMBER,
        p_items       IN item_list_type,
        p_order_id    OUT NUMBER
    );

    PROCEDURE approve_order(p_order_id IN NUMBER, p_approver IN VARCHAR2);
    PROCEDURE cancel_order(p_order_id IN NUMBER, p_reason IN VARCHAR2);
END order_service;

Service packages coordinate calls to multiple table APIs and implement transaction boundaries. APEX page processes call service layer procedures rather than issuing direct DML.

Constants and Configuration Packages

CREATE OR REPLACE PACKAGE app_constants AS
    c_status_draft     CONSTANT VARCHAR2(10) := 'DRAFT';
    c_status_submitted CONSTANT VARCHAR2(10) := 'SUBMITTED';
    c_status_approved  CONSTANT VARCHAR2(10) := 'APPROVED';
    c_max_order_amount CONSTANT NUMBER := 100000;
    c_default_currency CONSTANT VARCHAR2(3) := 'USD';
END app_constants;

Reference these constants throughout your code instead of scattering magic strings and numbers. When a value changes, you update it in one place.

Package Initialization

The initialization section at the end of a package body runs once per session when the package is first referenced. Use it for expensive one-time setup like loading configuration from a table into package variables. Keep initialization lightweight and handle exceptions, because a failed initialization makes the entire package unusable for that session.