Oracle PL/SQL – Packages

Subject: Information Technology – Database Systems
Grade: 12th Grade – HTL Computer Science
Prerequisites: Procedures and Functions, Cursors, Exceptions
Author: HTL Pinkafeld – IF/IT

1. What is a Package?

1.1 Definition

A package is a named PL/SQL unit that groups logically related types, variables, constants, cursors, procedures, and functions. It consists of two parts:

Package Specification (Spec)    Package Body
─────────────────────────────   ─────────────────────────────
Public interface                 Implementation
  • Types                          • Procedure/function code
  • Variables/constants            • Private elements
  • Cursor declarations            • Initialization section
  • Procedure/function headers

1.2 Advantages of Packages

2. Package Specification

2.1 Syntax

CREATE [OR REPLACE] PACKAGE package_name IS
    -- Public types, constants, variables
    -- Cursor declarations (header only)
    -- Procedure/function headers (without body)
END [package_name];
/

2.2 Example: pkg_employee

CREATE OR REPLACE PACKAGE pkg_employee IS

    -- Constants
    c_min_salary CONSTANT NUMBER := 1700;
    c_max_salary CONSTANT NUMBER := 50000;

    -- Public type
    TYPE t_emp_info IS RECORD (
        empno  emp.empno%TYPE,
        ename  emp.ename%TYPE,
        sal    emp.sal%TYPE,
        dept   dept.dname%TYPE
    );

    -- Procedure headers (interface)
    PROCEDURE give_raise   (p_empno emp.empno%TYPE, p_percent NUMBER);
    PROCEDURE show_dept    (p_deptno dept.deptno%TYPE);

    -- Function headers
    FUNCTION annual_salary (p_empno emp.empno%TYPE) RETURN NUMBER;
    FUNCTION is_manager    (p_empno emp.empno%TYPE) RETURN BOOLEAN;

    -- Public cursor type
    TYPE t_emp_cursor IS REF CURSOR RETURN emp%ROWTYPE;

END pkg_employee;
/

3. Package Body

3.1 Syntax

CREATE [OR REPLACE] PACKAGE BODY package_name IS
    -- Private variables, types (not visible in Spec)
    -- Implementation of all Spec-declared subprograms
    -- Additional private subprograms
    [BEGIN
        -- Initialization section (runs once at first call)
    ]
END [package_name];
/

3.2 Implementation of pkg_employee

CREATE OR REPLACE PACKAGE BODY pkg_employee IS

    -- Private variable (only visible in body)
    v_call_count NUMBER := 0;

    PROCEDURE give_raise (p_empno emp.empno%TYPE, p_percent NUMBER) IS
        v_old emp.sal%TYPE;
        v_new emp.sal%TYPE;
    BEGIN
        IF p_percent <= 0 OR p_percent > 50 THEN
            RAISE_APPLICATION_ERROR(-20010, 'Invalid percent: ' || p_percent);
        END IF;

        SELECT sal INTO v_old FROM emp WHERE empno = p_empno;
        v_new := ROUND(v_old * (1 + p_percent / 100), 2);

        IF v_new > c_max_salary THEN
            RAISE_APPLICATION_ERROR(-20011, 'Salary would exceed maximum.');
        END IF;

        UPDATE emp SET sal = v_new WHERE empno = p_empno;
        COMMIT;
        DBMS_OUTPUT.PUT_LINE('Salary: ' || v_old || ' → ' || v_new);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20012, 'Employee ' || p_empno || ' not found.');
    END give_raise;

    PROCEDURE show_dept (p_deptno dept.deptno%TYPE) IS
    BEGIN
        v_call_count := v_call_count + 1;
        DBMS_OUTPUT.PUT_LINE('--- Department ' || p_deptno || ' ---');
        FOR r IN (
            SELECT e.ename, e.job, e.sal
            FROM   emp e
            WHERE  e.deptno = p_deptno
            ORDER  BY e.sal DESC
        ) LOOP
            DBMS_OUTPUT.PUT_LINE(RPAD(r.ename, 12) || RPAD(r.job, 10) || r.sal);
        END LOOP;
    END show_dept;

    FUNCTION annual_salary (p_empno emp.empno%TYPE) RETURN NUMBER IS
        v_sal  emp.sal%TYPE;
        v_comm emp.comm%TYPE;
    BEGIN
        SELECT sal, NVL(comm, 0) INTO v_sal, v_comm
        FROM emp WHERE empno = p_empno;
        RETURN (v_sal + v_comm) * 12;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN RETURN NULL;
    END annual_salary;

    FUNCTION is_manager (p_empno emp.empno%TYPE) RETURN BOOLEAN IS
        v_cnt NUMBER;
    BEGIN
        SELECT COUNT(*) INTO v_cnt FROM emp WHERE mgr = p_empno;
        RETURN v_cnt > 0;
    END is_manager;

END pkg_employee;
/

3.3 Calling the Package

BEGIN
    -- Package elements called via dot notation:
    pkg_employee.give_raise(7369, 5);
    pkg_employee.show_dept(20);

    IF pkg_employee.is_manager(7839) THEN
        DBMS_OUTPUT.PUT_LINE('King is a manager.');
    END IF;

    DBMS_OUTPUT.PUT_LINE(
        'Annual salary King: ' || pkg_employee.annual_salary(7839)
    );
END;
/

4. Package Variables and Session State

4.1 Lifetime

Package variables live for the entire database connection (session). Each session has its own copy:

CREATE OR REPLACE PACKAGE pkg_counter IS
    v_total_calls NUMBER := 0;
    PROCEDURE count_call (p_action VARCHAR2);
    FUNCTION  get_count   RETURN NUMBER;
END pkg_counter;
/

CREATE OR REPLACE PACKAGE BODY pkg_counter IS
    PROCEDURE count_call (p_action VARCHAR2) IS
    BEGIN
        v_total_calls := v_total_calls + 1;
        DBMS_OUTPUT.PUT_LINE('[' || v_total_calls || '] ' || p_action);
    END;

    FUNCTION get_count RETURN NUMBER IS
    BEGIN
        RETURN v_total_calls;
    END;
END pkg_counter;
/

BEGIN
    pkg_counter.count_call('Login');
    pkg_counter.count_call('Query');
    pkg_counter.count_call('Update');
    DBMS_OUTPUT.PUT_LINE('Total: ' || pkg_counter.get_count());
END;
/

5. Overloading in Packages

Same name, different signatures:

CREATE OR REPLACE PACKAGE pkg_format IS
    FUNCTION format_val (p_num  NUMBER,  p_mask VARCHAR2 DEFAULT 'FM999,990.00') RETURN VARCHAR2;
    FUNCTION format_val (p_date DATE,    p_mask VARCHAR2 DEFAULT 'DD/MM/YYYY')   RETURN VARCHAR2;
    FUNCTION format_val (p_text VARCHAR2) RETURN VARCHAR2;
END pkg_format;
/

CREATE OR REPLACE PACKAGE BODY pkg_format IS
    FUNCTION format_val (p_num NUMBER, p_mask VARCHAR2 DEFAULT 'FM999,990.00') RETURN VARCHAR2 IS
    BEGIN RETURN TO_CHAR(p_num, p_mask); END;

    FUNCTION format_val (p_date DATE, p_mask VARCHAR2 DEFAULT 'DD/MM/YYYY') RETURN VARCHAR2 IS
    BEGIN RETURN TO_CHAR(p_date, p_mask); END;

    FUNCTION format_val (p_text VARCHAR2) RETURN VARCHAR2 IS
    BEGIN RETURN '"' || TRIM(p_text) || '"'; END;
END pkg_format;
/

6. Private and Public Elements

CREATE OR REPLACE PACKAGE pkg_demo IS
    FUNCTION public_function (p_x NUMBER) RETURN NUMBER;
END pkg_demo;
/

CREATE OR REPLACE PACKAGE BODY pkg_demo IS
    -- PRIVATE (only for internal use):
    FUNCTION private_helper (p_x NUMBER) RETURN NUMBER IS
    BEGIN RETURN p_x * p_x; END;

    -- Public function uses private one:
    FUNCTION public_function (p_x NUMBER) RETURN NUMBER IS
    BEGIN RETURN private_helper(p_x) + 1; END;
END pkg_demo;
/

7. Initialization Section

The BEGIN section at the end of the Package Body runs exactly once per session – on the first call to any package element:

CREATE OR REPLACE PACKAGE pkg_config IS
    v_server_name  VARCHAR2(50);
    v_db_version   VARCHAR2(20);
    v_start_time   DATE;
END pkg_config;
/

CREATE OR REPLACE PACKAGE BODY pkg_config IS
BEGIN
    -- One-time initialization:
    SELECT host_name INTO v_server_name FROM v$instance;
    SELECT version   INTO v_db_version  FROM v$instance;
    v_start_time := SYSDATE;
    DBMS_OUTPUT.PUT_LINE('Package initialized: ' || TO_CHAR(v_start_time, 'HH24:MI:SS'));
EXCEPTION
    WHEN OTHERS THEN
        v_server_name := 'Unknown';
        v_db_version  := 'Unknown';
END pkg_config;
/

8. Important Oracle Standard Packages

8.1 DBMS_OUTPUT

BEGIN
    DBMS_OUTPUT.ENABLE(1000000);
    DBMS_OUTPUT.PUT_LINE('Line 1');
    DBMS_OUTPUT.PUT('Partial text ');
    DBMS_OUTPUT.NEW_LINE;
END;
/

8.2 DBMS_UTILITY

DECLARE
    v_start NUMBER;
    v_end   NUMBER;
BEGIN
    v_start := DBMS_UTILITY.GET_TIME();
    FOR i IN 1..100000 LOOP NULL; END LOOP;
    v_end := DBMS_UTILITY.GET_TIME();
    DBMS_OUTPUT.PUT_LINE('Duration: ' || (v_end - v_start) || ' cs');
END;
/

8.3 Commonly Used Standard Packages

9. Managing Packages

-- Compile package spec and body:
ALTER PACKAGE pkg_employee COMPILE;
ALTER PACKAGE pkg_employee COMPILE BODY;

-- Check status of all packages:
SELECT object_name, object_type, status, last_ddl_time
FROM   user_objects
WHERE  object_type IN ('PACKAGE', 'PACKAGE BODY')
ORDER  BY object_name, object_type;

-- Show compile errors:
SELECT line, position, text
FROM   user_errors
WHERE  name = 'PKG_EMPLOYEE'
ORDER  BY type, sequence;

-- Drop body only (spec remains):
DROP PACKAGE BODY pkg_employee;

-- Drop spec and body:
DROP PACKAGE pkg_employee;

10. Summary and Outlook

10.1 Package Structure at a Glance

pkg_employee (Package)
├── Specification (public interface)
│   ├── Constants: c_min_salary, c_max_salary
│   ├── Types: t_emp_info, t_emp_cursor
│   ├── Procedures: give_raise, show_dept
│   └── Functions: annual_salary, is_manager
└── Body (implementation)
    ├── Private variable: v_call_count
    ├── Implementation of all Spec subprograms
    └── Private helper procedures/functions

10.2 When to Use a Package?

10.3 Outlook

The final chapter covers Triggers – PL/SQL code that executes automatically when certain database events occur:

• DML triggers (BEFORE/AFTER INSERT, UPDATE, DELETE)
• Row-level vs. statement-level triggers
• :NEW and :OLD pseudo-records
• INSTEAD OF triggers on views
• DDL and system triggers