What is a Package?

A Package is a database object that groups together related procedures, functions, variables, cursors, exceptions, and types into a single unit.

A package consists of two parts:

1. Package Specification (Declaration) – Declares public procedures, functions, variables, constants, cursors, etc.
2. Package Body (Implementation) – Contains the actual code for the procedures and functions declared in the specification.

Advantages of Packages

• Code Reusability – Group related program units together.
• Modularity – Organizes code into logical modules.
• Better Performance – Entire package is loaded into memory on first use.
• Security – Hide implementation details in the package body.
• Easy Maintenance – Modify the package body without changing the specification (if the interface remains the same).

Structure of a Package

Package
│
├── Package Specification
│      ├── Variables
│      ├── Constants
│      ├── Procedures
│      ├── Functions
│      └── Cursors
│
└── Package Body
       ├── Procedure Implementation
       ├── Function Implementation
       └── Private Variables/Procedures

Syntax

Step 1: Create Package Specification

CREATE OR REPLACE PACKAGE package_name AS

    -- Variable
    variable_name datatype;

    -- Procedure Declaration
    PROCEDURE procedure_name(parameter datatype);

    -- Function Declaration
    FUNCTION function_name(parameter datatype)
    RETURN datatype;

END package_name;
/

Step 2: Create Package Body

CREATE OR REPLACE PACKAGE BODY package_name AS

    PROCEDURE procedure_name(parameter datatype)
    IS
    BEGIN
        -- Procedure code
    END;

    FUNCTION function_name(parameter datatype)
    RETURN datatype
    IS
    BEGIN
        -- Function code
        RETURN value;
    END;

END package_name;
/

Example 1: Simple Package

Package Specification

CREATE OR REPLACE PACKAGE employee_pkg AS

    PROCEDURE display_message;

END employee_pkg;
/

Package Body

CREATE OR REPLACE PACKAGE BODY employee_pkg AS

    PROCEDURE display_message
    IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Welcome to Oracle Package');
    END display_message;

END employee_pkg;
/

Execute

EXEC employee_pkg.display_message;

Output

Welcome to Oracle Package

Example 2: Package with Procedure and Function

Employee Table

Package Specification

CREATE OR REPLACE PACKAGE emp_pkg AS

    PROCEDURE show_employee(p_empid NUMBER);

    FUNCTION get_salary(p_empid NUMBER)
    RETURN NUMBER;

END emp_pkg;
/

Package Body

CREATE OR REPLACE PACKAGE BODY emp_pkg AS

    PROCEDURE show_employee(p_empid NUMBER)
    IS
        v_name EMPLOYEE.EMP_NAME%TYPE;
        v_salary EMPLOYEE.SALARY%TYPE;
    BEGIN
        SELECT EMP_NAME, SALARY
        INTO v_name, v_salary
        FROM EMPLOYEE
        WHERE EMP_ID = p_empid;

        DBMS_OUTPUT.PUT_LINE('Employee Name : ' || v_name);
        DBMS_OUTPUT.PUT_LINE('Salary : ' || v_salary);

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Employee Not Found');
    END;

    FUNCTION get_salary(p_empid NUMBER)
    RETURN NUMBER
    IS
        v_salary EMPLOYEE.SALARY%TYPE;
    BEGIN
        SELECT SALARY
        INTO v_salary
        FROM EMPLOYEE
        WHERE EMP_ID = p_empid;

        RETURN v_salary;
    END;

END emp_pkg;
/

Execute Procedure

EXEC emp_pkg.show_employee(101);

Output

Employee Name : Rahul
Salary : 35000

Execute Function

DECLARE
    v_salary NUMBER;
BEGIN
    v_salary := emp_pkg.get_salary(101);

    DBMS_OUTPUT.PUT_LINE('Salary = ' || v_salary);
END;
/

Output

Salary = 35000

Example 3: Package Variable

Package Specification

CREATE OR REPLACE PACKAGE company_pkg AS

    company_name VARCHAR2(50) := 'ABC Technologies';

END company_pkg;
/

Use Package Variable

BEGIN
    DBMS_OUTPUT.PUT_LINE(company_pkg.company_name);
END;
/

Output

ABC Technologies

Example 4: Private Procedure

A procedure declared only in the package body is private and cannot be called from outside the package.

CREATE OR REPLACE PACKAGE BODY employee_pkg AS

    PROCEDURE private_message
    IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Private Procedure');
    END;

    PROCEDURE display_message
    IS
    BEGIN
        private_message;
    END;

END employee_pkg;
/

Calling:

EXEC employee_pkg.display_message;

Output:

Private Procedure

Trying to execute:

EXEC employee_pkg.private_message;

will result in an error because private_message is not declared in the package specification.

View Package Information

View Package Source

SELECT TEXT
FROM USER_SOURCE
WHERE NAME = 'EMP_PKG'
ORDER BY LINE;

View All Packages

SELECT OBJECT_NAME
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'PACKAGE';

Drop Package

DROP PACKAGE emp_pkg;

Difference Between Procedure and Package

Difference Between Package Specification and Package Body