What is Packages?
A PL/SQL Package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms (procedures and functions), cursors, and exceptions. Think of it as a container or a library that bundles related functionality together.
The package specification defines the interface (the public API), while the package body contains the implementation and private code.
Package Specification
The package specification (or spec) is the public face of the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that are accessible to other program units.
Anything not declared in the spec is private to the package body and hidden from the outside world.
CREATE OR REPLACE PACKAGE employee_api AS
-- Package Variable (Public Constant)
c_default_department CONSTANT VARCHAR2(10) := 'HR';
-- Package Cursor Specification (Exposes its structure)
CURSOR c_emp_dept (p_dept_id NUMBER) RETURN employees%ROWTYPE;
-- Package Procedure Specification
PROCEDURE get_employee_details (p_emp_id IN NUMBER);
-- Package Function Specification
FUNCTION get_employee_salary (p_emp_id IN NUMBER) RETURN NUMBER;
-- Overloaded Procedure Specifications
PROCEDURE update_salary (p_emp_id IN NUMBER, p_new_salary IN NUMBER);
PROCEDURE update_salary (p_emp_id IN NUMBER, p_percent_raise IN NUMBER);
END employee_api;
/
Package Body
The package body contains the complete implementation of all subprograms and cursors declared in the specification. It can also contain additional, private variables, cursors, and subprograms that are not exposed publicly.
Use the package body to hide implementation details and maintain encapsulation.
CREATE OR REPLACE PACKAGE BODY employee_api AS
-- Package Variable (Private, not in spec)
v_company_ceo_id NUMBER := 100;
-- Package Cursor Body Implementation
CURSOR c_emp_dept (p_dept_id NUMBER) RETURN employees%ROWTYPE IS
SELECT * FROM employees WHERE department_id = p_dept_id;
-- Private Function (Not declared in spec, hidden from outside)
FUNCTION is_employee_active(p_emp_id NUMBER) RETURN BOOLEAN IS
l_status VARCHAR2(10);
BEGIN
SELECT status INTO l_status FROM emp_status WHERE emp_id = p_emp_id;
RETURN l_status = 'ACTIVE';
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN FALSE;
END is_employee_active;
-- Public Procedure Body Implementation
PROCEDURE get_employee_details (p_emp_id IN NUMBER) IS
l_emp employees%ROWTYPE;
BEGIN
SELECT * INTO l_emp FROM employees WHERE employee_id = p_emp_id;
DBMS_OUTPUT.PUT_LINE('Name: ' || l_emp.first_name || ' ' || l_emp.last_name);
-- Example of using private function
IF is_employee_active(p_emp_id) THEN
DBMS_OUTPUT.PUT_LINE('Status: Active');
END IF;
END get_employee_details;
-- Public Function Body Implementation
FUNCTION get_employee_salary (p_emp_id IN NUMBER) RETURN NUMBER IS
l_salary NUMBER;
BEGIN
SELECT salary INTO l_salary FROM employees WHERE employee_id = p_emp_id;
RETURN l_salary;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN 0;
END get_employee_salary;
-- Overloaded Procedure Body Implementations
PROCEDURE update_salary (p_emp_id IN NUMBER, p_new_salary IN NUMBER) IS
BEGIN
UPDATE employees SET salary = p_new_salary WHERE employee_id = p_emp_id;
END update_salary;
PROCEDURE update_salary (p_emp_id IN NUMBER, p_percent_raise IN NUMBER) IS
l_old_salary NUMBER;
BEGIN
l_old_salary := get_employee_salary(p_emp_id); -- Calling our own package function
UPDATE employees SET salary = l_old_salary * (1 + p_percent_raise/100)
WHERE employee_id = p_emp_id;
END update_salary;
END employee_api;
/
Package Variables
These are variables declared at the package level (in the spec or body). Their state persists for the duration of a session.
Public variables (declared in the spec) can be modified by any user with execute privileges. Private variables are safer for internal package state.
Package Cursors
A cursor can be declared in a package spec or body. A package-level cursor provides a way to standardize a query across multiple subprograms. It can accept parameters, making it flexible.
-- A program outside the package could open this cursor
OPEN employee_api.c_emp_dept(50);
Package Procedures & Functions
These are procedures and functions defined within a package. The key advantage is that they are organized within a logical namespace (the package name).
Calling them requires the package name as a prefix, preventing naming collisions.
-- Call a package function
DECLARE
v_salary NUMBER;
BEGIN
v_salary := employee_api.get_employee_salary(101);
END;
/
Package Initialization
Package initialization is an optional block of code at the end of the package body that runs once per session, the first time any member of the package is referenced. It is used for one-time setup.
CREATE OR REPLACE PACKAGE BODY employee_api AS
-- A private package collection to cache department names
TYPE t_dept_cache IS TABLE OF departments.department_name%TYPE INDEX BY PLS_INTEGER;
g_dept_cache t_dept_cache;
-- ... (other package body code) ...
-- Package Initialization Section
BEGIN
DBMS_OUTPUT.PUT_LINE('Initializing employee_api package for this session.');
-- Pre-populate the cache
FOR rec IN (SELECT department_id, department_name FROM departments) LOOP
g_dept_cache(rec.department_id) := rec.department_name;
END LOOP;
END employee_api;
/
Overloading Procedures
Overloading allows you to define multiple subprograms with the same name within the same package. They must differ in the number, order, or data type family of their formal parameters.
Overloading provides a clean, intuitive interface for functions that perform similar operations with different inputs.
-- Example: Two versions of update_salary
-- 1. Accept absolute value
employee_api.update_salary(p_emp_id => 100, p_new_salary => 75000);
-- 2. Accept percentage
employee_api.update_salary(p_emp_id => 100, p_percent_raise => 10);
Why is Packages Important?
Encapsulation (Modularity)
Packages bundle related code and data, hiding implementation details behind a clean specification. This adheres to the principle of Information Hiding, making code easier to understand, maintain, and debug.
FAST Dependency Principle: When a change is made to a package body, dependent objects (those that call the package’s public spec) are not invalidated, as long as the spec remains unchanged. This minimizes costly recompilation cascades.
Code Reusability (DRY)
By creating a centralized library of common functions and procedures (e.g., date_utilities, security_api), packages prevent code duplication across the application.
Advanced Nuances
Persistent State and Session Memory
Package variables persist for the duration of a database session. In connection pools, this can lead to unexpected state from previous users.
PRAGMA SERIALLY_REUSABLE to make package state exist only for the duration of a single call, which is crucial for high-throughput systems.
AUTHID Clause (Definer vs. Invoker’s Rights)
A package can be compiled with AUTHID DEFINER (default) or AUTHID CURRENT_USER. With definer’s rights, the package executes with the privileges of the owner, not the caller.
CREATE OR REPLACE PACKAGE secure_api AUTHID DEFINER AS
-- Executes with owner's privileges
END secure_api;
Overloading Limitations
You cannot overload functions based solely on their return type or subprograms where the parameter types are in the same family (e.g., NUMBER and INTEGER).
How This Fits the Roadmap
Within the “PL/SQL Programming” section, Packages are the culmination of fundamental programming constructs (variables, loops, cursors, procedures, functions).
Prerequisites:
- Variables, loops, and control structures
- Procedures and functions
- Cursors
Unlocks:
- Advanced API Design
- Application Security Models (using
AUTHID)
- Performance Optimization (using packages for caching)
- Database Architecture & Advanced Internals
- Performance Tuning techniques
Packages are the foundational pattern for building enterprise-grade PL/SQL applications, moving beyond scripting to structured software engineering.