PL/SQL Package Design: Principles and Best Practices

Why packages matter

Packages group related procedures, functions, types, variables, and cursors into a single, named unit. Unlike standalone procedures, packages:

• Enable encapsulation (private vs public members)
• Improve performance (entire package loads into shared pool on first call)
• Allow forward declarations (procedures can call each other in any order)
• Persist session state via package variables

Package structure

Every package has two parts:

Specification (public interface):

CREATE OR REPLACE PACKAGE pkg_order_mgmt AS
  -- Types
  TYPE t_order_rec IS RECORD (...);
  -- Constants
  c_status_open CONSTANT VARCHAR2(10) := 'OPEN';
  -- Public procedures
  PROCEDURE create_order(p_customer_id NUMBER, p_amount NUMBER);
  FUNCTION get_order_status(p_order_id NUMBER) RETURN VARCHAR2;
END pkg_order_mgmt;

Body (implementation):

CREATE OR REPLACE PACKAGE BODY pkg_order_mgmt AS
  -- Private variables/procedures only visible here
  g_last_order_id NUMBER;
  
  PROCEDURE validate_order(p_amount NUMBER) IS ...
  
  PROCEDURE create_order(p_customer_id NUMBER, p_amount NUMBER) IS
  BEGIN
    validate_order(p_amount); -- call private procedure
    ...
  END;
END pkg_order_mgmt;

Naming conventions

• Package: pkg_<domain> or <domain>_pkg
• Public procedures: verb-noun (create_order, get_status)
• Private procedures: prefixed with p_ or prv_
• Package constants: c_ prefix
• Package variables: g_ prefix (global to session)

Package state and session variables

Package-level variables persist for the database session:

-- Set in one call:
pkg_context.set_user_bunit(p_bunit_id);
-- Read in subsequent calls without re-fetching:
v_bunit := pkg_context.get_user_bunit;

Useful for session context, but be aware: in a connection pool environment, connections are reused across user requests. Always reset package state explicitly rather than relying on previous call state.

Testing packages

Write a separate test package for each business package:

CREATE OR REPLACE PACKAGE pkg_order_mgmt_test AS
  PROCEDURE test_create_order;
  PROCEDURE test_invalid_amount;
  PROCEDURE run_all_tests;
END;

Use utPLSQL or a custom test framework to run and report results as part of your CI/CD pipeline.

Package recompilation impact

Changing the package specification invalidates all dependent objects (packages, views, procedures that call it). Changing only the body does not. Keep specifications stable — add new public members rather than changing signatures of existing ones.