Oracle PL SQL Tutorial Day 13
Introduction
Oracle PL/SQL is a powerful procedural language tightly integrated with SQL, providing advanced features for developing robust database applications. One such feature is the PL/SQL Package, a modular programming construct that groups logically related subprograms, variables, types, and exceptions into a single unit. In this article, we’ll explore what PL/SQL packages are, why they're useful, and how to create and use them effectively with real-world examples using Oracle’s HR schema.
What is a PL/SQL Package?
A PL/SQL package is a schema object that groups logically related PL/SQL types, items, and subprograms. A package always has two parts:
- Package Specification – This defines the public interface (procedures, functions, types, variables).
- Package Body – This contains the actual implementation of the declared subprograms.
Benefits of PL/SQL Packages
- Modularity: Organize code into logical units.
- Encapsulation: Hide implementation details from users. So the user or consumer does not need to know behind the scenes they simply call and achieve the purpose. Remember, Oracle PL SQL is a modular language so it doesn't have same features like other object oriented programming language like java or c#. Encapsulation is limited.
- Code Reusability: Shared procedures and functions across applications. These packages can be use by other database schemas/users, oracle forms, oracle apex and other integrated applications e.g java, MS .NET. oracle provide strong security hence related execution rights must be provided before use them
- Performance: Loaded into memory only once, reducing call overhead. After compilation oracle database keeps the code in memory as pssedu form.
- Maintainability: Easy to manage and modify without affecting other components. Incase package specifications changed you might need to re compile oracle forms if same called in forms directly
Package Syntax
Package Specification:
CREATE OR REPLACE PACKAGE package_name IS
-- public variables, types, exceptions
-- procedure/function declarations
END package_name;
Package Body:
CREATE OR REPLACE PACKAGE BODY package_name IS
-- implementation of procedures/functions
END package_name;
Example 1: Creating a Simple Package
Step 1: Package Specification
CREATE OR REPLACE PACKAGE emp_bonus_pkg IS
bonus_rate NUMBER := 0.10;
PROCEDURE set_bonus_rate(p_rate NUMBER);
FUNCTION calculate_bonus(p_emp_id NUMBER) RETURN NUMBER;
END emp_bonus_pkg;
Step 2: Package Body
CREATE OR REPLACE PACKAGE BODY emp_bonus_pkg IS
PROCEDURE set_bonus_rate(p_rate NUMBER) IS
BEGIN
bonus_rate := p_rate;
END set_bonus_rate;
FUNCTION calculate_bonus(p_emp_id NUMBER) RETURN NUMBER IS
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary
FROM employees
WHERE employee_id = p_emp_id;
RETURN v_salary * bonus_rate;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END calculate_bonus;
END emp_bonus_pkg;
Keep in mind, packages and procure name should be 30 character max. Function or procedure overloading can be achieve via packages. Any variable define at package specifications will be a public variable which available entire package. Package body and procedure can be compile separately using alter command
Step 3: Using the Package
BEGIN
emp_bonus_pkg.set_bonus_rate(0.15);
DBMS_OUTPUT.PUT_LINE('Bonus: ' || emp_bonus_pkg.calculate_bonus(100));
END;
Example 2: Package with Private Subprogram
Package Specification:
CREATE OR REPLACE PACKAGE emp_tools_pkg IS
PROCEDURE print_employee_details(p_emp_id NUMBER);
END emp_tools_pkg;
Package Body:
CREATE OR REPLACE PACKAGE BODY emp_tools_pkg IS
-- Private procedure
PROCEDURE log_access(p_emp_id NUMBER) IS
BEGIN
INSERT INTO access_log(emp_id, access_time)
VALUES (p_emp_id, SYSDATE);
END log_access;
PROCEDURE print_employee_details(p_emp_id NUMBER) IS
v_name employees.first_name%TYPE;
v_job employees.job_id%TYPE;
BEGIN
SELECT first_name, job_id
INTO v_name, v_job
FROM employees
WHERE employee_id = p_emp_id;
log_access(p_emp_id);
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
DBMS_OUTPUT.PUT_LINE('Job: ' || v_job);
END print_employee_details;
END emp_tools_pkg;
Example 3: Package State and Persistent Variables
Package Specification:
CREATE OR REPLACE PACKAGE counter_pkg IS
PROCEDURE increment;
FUNCTION get_count RETURN NUMBER;
END counter_pkg;
Package Body:
CREATE OR REPLACE PACKAGE BODY counter_pkg IS
v_count NUMBER := 0;
PROCEDURE increment IS
BEGIN
v_count := v_count + 1;
END increment;
FUNCTION get_count RETURN NUMBER IS
BEGIN
RETURN v_count;
END get_count;
END counter_pkg;
Usage:
BEGIN
counter_pkg.increment;
counter_pkg.increment;
DBMS_OUTPUT.PUT_LINE('Current count: ' || counter_pkg.get_count); -- Output: 2
END;
Example 4: Overloading in Packages
Package Specification:
CREATE OR REPLACE PACKAGE overload_pkg IS
PROCEDURE log(p_msg VARCHAR2);
PROCEDURE log(p_code NUMBER);
END overload_pkg;
Package Body:
CREATE OR REPLACE PACKAGE BODY overload_pkg IS
PROCEDURE log(p_msg VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Message: ' || p_msg);
END;
PROCEDURE log(p_code NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Code: ' || p_code);
END;
END overload_pkg;
Usage:
BEGIN
overload_pkg.log('System initialized');
overload_pkg.log(404);
END;
Best Practices
- Group related procedures/functions logically. Like HR module have a single or multiple package, finance module have separate package, utility package is separate.
- Expose only what’s necessary; hide internals.
- Use constants for configuration or shared values.
- Use exceptions consistently in public and private subprograms.
- Avoid too much state if used in multi-user environments.
Package vs Procedure vs Function
---------------------------------
| Feature | Package | Procedure | Function |
|------------------|--------------------------|--------------------------|-----------------------|
| Structure | Grouped code | Single block | Single block |
| Encapsulation | Yes | No | No |
| Return value | Optional (via functions) | No | Yes |
| Overloading | Yes | No | No |
| Global variables | Yes | No | No |
| Maintainability | High | Moderate | Moderate |
Debugging and Managing Packages
Check compilation errors:
SELECT * FROM user_errors WHERE name = 'EMP_BONUS_PKG';
View source:
SELECT text FROM user_source WHERE name = 'EMP_BONUS_PKG' ORDER BY line;
Drop a package:
DROP PACKAGE emp_bonus_pkg;
Drop specification or body separately:
DROP PACKAGE BODY emp_bonus_pkg;
Conclusion
PL/SQL packages are one of the most powerful tools in Oracle’s procedural arsenal. By grouping related logic, exposing clean interfaces, and hiding implementation details, packages improve both code organization and application performance. As your PL/SQL applications grow, packages help manage complexity, encourage reuse, and ensure consistency across modules.
Appropriate execution rights must be provided to run or execute the packages. If package using other schema's objects those must be exposed as well to current schema.
From maintaining application state to encapsulating business logic, packages serve as a foundational design component in professional PL/SQL development. Mastering them is essential for building scalable and maintainable Oracle database applications.
Hopefully you got understanding of packages through this article we will explore more in upcoming lessons including real time business examples
0 Comments