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.