Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 38 ยท Packages and Triggers

Oracle PL/SQL Packages: Specification and Body

Learn how Oracle packages group procedures, functions, types and constants. Write a specification and body, call public members, hide private code and manage dependencies.

Once a project has dozens of procedures and functions, a flat list of them becomes hard to manage. A package is Oracle's answer: a schema object that groups related procedures, functions, cursors, types, constants and variables under one name. Packages give you a clear public interface, hidden implementation details, better performance (the whole package loads into memory at the first call) and fewer invalidation headaches when you change internals.

Nearly all serious PL/SQL code is delivered in packages. Oracle's own libraries (DBMS_OUTPUT, UTL_FILE, DBMS_SCHEDULER) are packages, and you already call them with the package.member dot syntax.

Oracle cannot run on this site, so package code is read-only. The runnable example is a Python module acting as a stand-in for the public/private split.

Two parts: specification and body

A package has a specification (the public interface, often called the "spec") and a body (the implementation).

SQL
CREATE OR REPLACE PACKAGE employee_api AS
  c_max_raise_percent CONSTANT NUMBER := 20;          -- public constant

  TYPE t_salary_summary IS RECORD (
    min_salary NUMBER,
    max_salary NUMBER,
    avg_salary NUMBER
  );

  PROCEDURE give_raise (p_employee_id IN NUMBER, p_percent IN NUMBER);
  FUNCTION  department_summary (p_dept IN NUMBER) RETURN t_salary_summary;
END employee_api;
/

The spec lists only declarations: names, parameters and return types, with no code. Anything declared here is visible to every user who has EXECUTE on the package.

SQL
CREATE OR REPLACE PACKAGE BODY employee_api AS

  -- private: not in the spec, so callers cannot see or call it
  FUNCTION is_valid_raise (p_percent NUMBER) RETURN BOOLEAN IS
  BEGIN
    RETURN p_percent BETWEEN 0 AND c_max_raise_percent;
  END is_valid_raise;

  PROCEDURE give_raise (p_employee_id IN NUMBER, p_percent IN NUMBER) IS
  BEGIN
    IF NOT is_valid_raise(p_percent) THEN
      RAISE_APPLICATION_ERROR(-20001, 'Raise must be 0 to ' || c_max_raise_percent || ' percent');
    END IF;
    UPDATE employees
       SET salary = salary * (1 + p_percent / 100)
     WHERE employee_id = p_employee_id;
  END give_raise;

  FUNCTION department_summary (p_dept IN NUMBER) RETURN t_salary_summary IS
    v_result t_salary_summary;
  BEGIN
    SELECT MIN(salary), MAX(salary), AVG(salary)
      INTO v_result.min_salary, v_result.max_salary, v_result.avg_salary
      FROM employees
     WHERE department_id = p_dept;
    RETURN v_result;
  END department_summary;

END employee_api;
/

Rules to remember:

  • Every subprogram in the spec must be defined in the body with an identical signature (names, parameter modes, types, defaults).
  • Anything defined in the body but not declared in the spec is private. It can be called only by other code in the package body. In the example, is_valid_raise is private.
  • Private subprograms must be defined before they are used in the body, or forward-declared at the top of the body.
  • The package spec can exist without a body if it contains only constants, types and exceptions.

Callers use the dot syntax:

SQL
BEGIN
  employee_api.give_raise(101, 10);
  DBMS_OUTPUT.PUT_LINE('Cap is ' || employee_api.c_max_raise_percent);
END;
/

Why the split matters

A package has two halves

Specification The public contract
Body The hidden implementation
Private code Only inside the body

Callers depend on the spec, not on the body.

1 / 2

Dependencies are the practical payoff. Code that calls employee_api.give_raise depends on the specification only. If you fix a bug in the body and recompile it, the spec stays valid and no caller is invalidated. If you instead changed a standalone procedure, everything that calls it would be marked INVALID until recompiled. Keep specs stable and put the churn in bodies.

The same idea in Python

The stand-in below is not Oracle. It uses a module-like class where a leading underscore marks "private" and only the names in __all__-style PUBLIC are exposed, mirroring a spec that lists the public interface while the body hides helpers.

Python Public interface, private helper (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Practical points

  • Naming. Name a package after its subject (employee_api, billing_pkg), not after a single action. A package with one function is just a function with extra steps.
  • Overloading. Several subprograms may share a name if their parameter lists differ; see package state and overloading.
  • Recompiling. Compile spec and body with ALTER PACKAGE employee_api COMPILE (body only: COMPILE BODY). Errors are in USER_ERRORS with TYPE = 'PACKAGE BODY'.
  • Source code. SELECT text FROM user_source WHERE name = 'EMPLOYEE_API' ORDER BY type, line; shows what is stored.
  • Security. Grant EXECUTE on the package, not on its tables. Callers can use the public interface and nothing else.
  • Dropping. DROP PACKAGE BODY employee_api; removes only the body; DROP PACKAGE employee_api; removes both.
  • Splitting large packages. Keep each package focused. A 20,000-line package is slow to compile and hard to test; split by area (order_api, billing_api).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which part of a package is visible to users who have EXECUTE on it?

    The specification is the public interface. Items only in the body are private.
  2. 2. A function is defined in the package body but not declared in the spec. What is it?

    Private subprograms can only be called from other code inside the package body.
  3. 3. Why does changing only the package body avoid invalidating callers?

    Dependencies are recorded against the spec, so a body recompile leaves dependants valid.
  4. 4. Which statement removes only the implementation and keeps the interface?

    DROP PACKAGE BODY leaves the spec in place (and callers become invalid until a body exists again).

Summary

  • A package groups related PL/SQL objects behind a public specification and a hidden body.
  • Spec items are public; body-only items are private and must be defined before use or forward-declared.
  • Callers depend on the spec, so body changes do not cascade invalidations.
  • Grant EXECUTE on the package for controlled access and name packages after their subject.

Next: Package State, Overloading and Initialization