PL/SQL Packages โ Syntax, Examples, Specification vs Body, and Best Use Cases
What is a Package in PL/SQL?
In PL/SQL, a package is a schema-level collection of related procedures, functions, variables, cursors, and exceptions grouped together under a single name. Packages provide modularity, encapsulation, and reusability for PL/SQL programs.
Think of a package as a library of subprogramsโcleanly organized and easy to reuse across different parts of your application.
Key Components of a Package
A PL/SQL package is divided into two major parts:
Package Specification (Spec)
The interface to the package
Contains the declarations of public variables, procedures, and functions
Defines what is accessible to the outside world
Package Body
The implementation of the package
Contains the actual code of the procedures and functions declared in the spec
May also include private elements that are not exposed in the spec
You can compile the specification independentlyโeven without creating the body right away.
Why Use Packages in PL/SQL?
| Benefit | Explanation |
|---|---|
| Modularity | Organize related code into a single structure |
| Encapsulation | Hide internal logic and expose only necessary components |
| Reusability | Share common logic (functions/procedures) across multiple PL/SQL blocks |
| Performance Boost | Loaded into memory once, reused across sessions |
| Easy Maintenance | Update one package instead of many standalone procedures |
Syntax of a PL/SQL Package
Package Specification Example:
CREATE OR REPLACE PACKAGE employee_pkg IS
PROCEDURE add_employee(p_id NUMBER, p_name VARCHAR2);
FUNCTION get_total_employees RETURN NUMBER;
END employee_pkg;
Package Body Example:
CREATE OR REPLACE PACKAGE BODY employee_pkg IS
total NUMBER := 0;
PROCEDURE add_employee(p_id NUMBER, p_name VARCHAR2) IS
BEGIN
-- Logic to add employee
total := total + 1;
END;
FUNCTION get_total_employees RETURN NUMBER IS
BEGIN
RETURN total;
END;
END employee_pkg;
How to Execute a Package Procedure or Function
BEGIN
employee_pkg.add_employee(1, 'John Doe');
DBMS_OUTPUT.PUT_LINE('Total Employees: ' || employee_pkg.get_total_employees);
END;
/
Package Specification vs Package Body
| Feature | Package Specification | Package Body |
|---|---|---|
| Purpose | Declares public interface | Implements declared procedures/functions |
| Visibility | Public | Private/internal |
| Compilation | Can exist alone | Depends on spec |
| Can contain variables? | Yes (public only) | Yes (public and private) |
| DESCRIBE visibility | Visible in DESCRIBE command | Not shown in DESCRIBE |
| Optional? | No | Yes (if no implementation needed) |
When to Use PL/SQL Packages
Use packages when:
- You want to organize code modules logically.
- You have many procedures and functions that work together.
- You want to reduce parsing time in large applications.
- You need to define constants and variables globally for reuse.
- You want to hide implementation details and expose only essential logic.
AmantPoint Exclusive Learning Series
ยฉ 2025 AmantPoint. All rights reserved. Educational content for developers and learners.
