Packages

A package is a schema object that groups related PL/SQL types, variables, procedures, and functions together. Packages are the primary way to organise and deploy PL/SQL code in production Oracle systems.

Two Parts of a Package

Every package has two separate components:

1. Package Specification (the interface)

CREATE OR REPLACE PACKAGE package_name AS
  -- Public declarations: visible to all callers
  TYPE ...;
  v_public_var datatype;
  PROCEDURE public_proc(p1 IN datatype);
  FUNCTION  public_func(p1 IN datatype) RETURN datatype;
END package_name;
/

2. Package Body (the implementation)

CREATE OR REPLACE PACKAGE BODY package_name AS
  -- Private variables: only visible inside this body
  v_private_var datatype;

  PROCEDURE public_proc(p1 IN datatype) AS
  BEGIN
    -- implementation
  END public_proc;

  FUNCTION public_func(p1 IN datatype) RETURN datatype AS
  BEGIN
    -- implementation
  END public_func;

END package_name;
/

A Practical Example

-- Spec
CREATE OR REPLACE PACKAGE emp_mgmt AS
  PROCEDURE hire   (p_name IN VARCHAR2, p_salary IN NUMBER);
  PROCEDURE fire   (p_id   IN NUMBER);
  FUNCTION  headcount RETURN NUMBER;
END emp_mgmt;
/

-- Body
CREATE OR REPLACE PACKAGE BODY emp_mgmt AS

  PROCEDURE hire(p_name IN VARCHAR2, p_salary IN NUMBER) AS
  BEGIN
    INSERT INTO employees(name, salary) VALUES (p_name, p_salary);
    COMMIT;
  END hire;

  PROCEDURE fire(p_id IN NUMBER) AS
  BEGIN
    DELETE FROM employees WHERE id = p_id;
    COMMIT;
  END fire;

  FUNCTION headcount RETURN NUMBER AS
    v_count NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_count FROM employees;
    RETURN v_count;
  END headcount;

END emp_mgmt;
/

Calling Package Subprograms

BEGIN
  emp_mgmt.hire('Bob', 60000);
END;
/

SELECT emp_mgmt.headcount FROM DUAL;

Use the package_name.subprogram_name dot notation.

Package State (Package Variables)

Variables declared in the package spec or body persist for the lifetime of a session. Every call within the same session shares the same variable values:

CREATE OR REPLACE PACKAGE session_info AS
  v_user VARCHAR2(100) := SYS_CONTEXT('USERENV', 'SESSION_USER');
END session_info;
/

Benefits of Packages

Benefit	Detail
Organisation	Group related code in one named unit
Encapsulation	Hide private helpers from callers
Performance	The entire package is loaded into memory on first use
Overloading	Multiple subprograms with the same name but different parameters
Global state	Package variables persist across calls in a session

Dropping a Package

DROP PACKAGE package_name;        -- drops spec and body
DROP PACKAGE BODY package_name;   -- drops body only