Oracle Database 23ai offers powerful features for modern application development, and packages are at the center of it. A package in PL/SQL is a way to group related procedures, functions, constants, cursors, and variables into a single unit. Instead of scattering logic across many standalone objects, you can organize them under one umbrella, making your applications modular, secure, and easier to maintain.
This Oracle tutorial explains how to create packages, the difference between a specification and a body, how to use parameters in procedures and functions, how to handle exceptions, and how to integrate new JSON and vector data types available in Oracle Database 23ai.
Syntax of a Package in Oracle Database 23ai
Every PL/SQL package is divided into two main components: the package specification and the package body. The specification acts like an interface, listing the available functions, procedures, and public variables. The body contains the actual implementation. If you only need to declare global constants or types, you can create a package specification without a body.
Package Specification Syntax
CREATE [OR REPLACE] PACKAGE package_name IS -- Public type declarations -- Public variable declarations -- Public procedure and function specifications PROCEDURE procedure_name (parameter_list); FUNCTION function_name (parameter_list) RETURN datatype; END package_name; /
Package Body Syntax
CREATE [OR REPLACE] PACKAGE BODY package_name IS
-- Private variables and helper functions (optional)
PROCEDURE procedure_name (parameter_list) IS
BEGIN
-- procedure logic
END;
FUNCTION function_name (parameter_list) RETURN datatype IS
BEGIN
-- function logic
RETURN value;
END;
END package_name;
/
The specification ensures users know what they can call, while the body provides the logic. This separation allows you to update the internal implementation without breaking applications that depend on the package.
Creating a Simple Package
A package can encapsulate related operations. For example, in an HR system, you may need to both update employee salaries and calculate annual salary. Instead of writing two standalone procedures or functions, you can group them inside a single package for better organization and reuse.
Create a sample table
CREATE TABLE prc_emp_pkg (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER(10,2),
department_id NUMBER
);
INSERT INTO prc_emp_pkg VALUES (301, 'Steve', 'Rogers', 7500, 10);
INSERT INTO prc_emp_pkg VALUES (302, 'Natasha', 'Romanoff', 9000, 20);
COMMIT;
Create the package specification
CREATE OR REPLACE PACKAGE prc_employee_pkg IS
PROCEDURE raise_salary (
p_emp_id IN NUMBER,
p_percent IN NUMBER
);
FUNCTION get_annual_salary (
p_emp_id IN NUMBER
) RETURN NUMBER;
END prc_employee_pkg;
/
This specification declares two public subprograms: raise_salary (a procedure) and get_annual_salary (a function). Applications can call them, but they cannot see how they are implemented inside the package body.
Create the package body
CREATE OR REPLACE PACKAGE BODY prc_employee_pkg IS
PROCEDURE raise_salary (
p_emp_id IN NUMBER,
p_percent IN NUMBER
) IS
BEGIN
UPDATE prc_emp_pkg
SET salary = salary + (salary * p_percent / 100)
WHERE employee_id = p_emp_id;
DBMS_OUTPUT.PUT_LINE('Salary updated for Employee ID: ' || p_emp_id);
END;
FUNCTION get_annual_salary (
p_emp_id IN NUMBER
) RETURN NUMBER IS
v_salary NUMBER;
BEGIN
SELECT salary * 12
INTO v_salary
FROM prc_emp_pkg
WHERE employee_id = p_emp_id;
RETURN v_salary;
END;
END prc_employee_pkg;
/
Here, raise_salary uses two IN parameters—the employee ID and the percentage increase—to update the salary. The get_annual_salary function accepts an employee ID and returns a single value (annual salary).
Execute the package
SET SERVEROUTPUT ON; BEGIN prc_employee_pkg.raise_salary(301, 20); END; / SELECT prc_employee_pkg.get_annual_salary(301) AS annual_salary FROM dual;
Output:
Salary updated for Employee ID: 301 ANNUAL_SALARY ------------- 108000
This shows how a package consolidates related logic into one place, improving code organization and reusability.
Creating a Package with IN, OUT, and IN OUT Parameters
Packages often need to pass multiple values back to the caller. This is where OUT and IN OUT parameters are useful. A package can contain procedures that take multiple input values and return multiple outputs.
Create the package specification
CREATE OR REPLACE PACKAGE prc_employee_info_pkg IS
PROCEDURE get_employee_details (
p_emp_id IN NUMBER,
p_fname OUT VARCHAR2,
p_lname OUT VARCHAR2,
p_salary OUT NUMBER
);
END prc_employee_info_pkg;
/
This declares a procedure get_employee_details with one input (p_emp_id) and three output parameters (p_fname, p_lname, p_salary).
Create the package body
CREATE OR REPLACE PACKAGE BODY prc_employee_info_pkg IS
PROCEDURE get_employee_details (
p_emp_id IN NUMBER,
p_fname OUT VARCHAR2,
p_lname OUT VARCHAR2,
p_salary OUT NUMBER
) IS
BEGIN
SELECT first_name, last_name, salary
INTO p_fname, p_lname, p_salary
FROM prc_emp_pkg
WHERE employee_id = p_emp_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_fname := NULL;
p_lname := NULL;
p_salary := NULL;
END;
END prc_employee_info_pkg;
/
The OUT parameters allow the procedure to return multiple pieces of information at once. If the employee ID does not exist, the procedure sets the outputs to NULL.
Execute the procedure
SET SERVEROUTPUT ON;
DECLARE
v_fname VARCHAR2(50);
v_lname VARCHAR2(50);
v_salary NUMBER;
BEGIN
prc_employee_info_pkg.get_employee_details(302, v_fname, v_lname, v_salary);
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_fname || ' ' || v_lname || ' | Salary = ' || v_salary);
END;
/
Output:
Employee: Natasha Romanoff | Salary = 9000
This makes it easy to return multiple related values from one call instead of executing multiple queries.
Creating a Package with JSON Functions in Oracle 23ai
Oracle Database 23ai has introduced a native JSON data type, making it faster and easier to work with semi-structured data. You can use packages to build reusable APIs for handling JSON documents, extracting values, or processing JSON content.
Create a JSON table
CREATE TABLE prc_customers_pkg (
customer_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
customer_data JSON
);
INSERT INTO prc_customers_pkg (customer_data) VALUES (
JSON('{"name": "Tony", "email": "tony@stark.com", "tier": "Platinum"}')
);
INSERT INTO prc_customers_pkg (customer_data) VALUES (
JSON('{"name": "Bruce", "email": "bruce@wayne.com", "tier": "Gold"}')
);
COMMIT;
Create the package
CREATE OR REPLACE PACKAGE prc_customer_pkg IS
FUNCTION get_customer_email (
p_customer_id IN NUMBER
) RETURN VARCHAR2;
PROCEDURE print_customer_details (
p_customer_id IN NUMBER
);
END prc_customer_pkg;
/
CREATE OR REPLACE PACKAGE BODY prc_customer_pkg IS
FUNCTION get_customer_email (
p_customer_id IN NUMBER
) RETURN VARCHAR2 IS
v_email VARCHAR2(100);
BEGIN
SELECT c.customer_data.email.string()
INTO v_email
FROM prc_customers_pkg c
WHERE c.customer_id = p_customer_id;
RETURN v_email;
END;
PROCEDURE print_customer_details (
p_customer_id IN NUMBER
) IS
v_name VARCHAR2(100);
v_email VARCHAR2(100);
v_tier VARCHAR2(50);
BEGIN
SELECT c.customer_data.name.string(),
c.customer_data.email.string(),
c.customer_data.tier.string()
INTO v_name, v_email, v_tier
FROM prc_customers_pkg c
WHERE c.customer_id = p_customer_id;
DBMS_OUTPUT.PUT_LINE('Customer: ' || v_name || ' | Email: ' || v_email || ' | Tier: ' || v_tier);
END;
END prc_customer_pkg;
/
Execute the JSON functions
SET SERVEROUTPUT ON; -- Call function in SQL SELECT prc_customer_pkg.get_customer_email(1) AS customer_email FROM dual; -- Call procedure BEGIN prc_customer_pkg.print_customer_details(2); END; /
Output:
CUSTOMER_EMAIL ----------------- tony@stark.com Customer: Bruce | Email: bruce@wayne.com | Tier: Gold
This shows how packages in Oracle 23ai can handle JSON efficiently using the new JSON type and direct field access methods like .string() and .number().
Creating a Package with Vector Functions in Oracle 23ai
Oracle 23ai introduces vector data types for storing and querying embeddings used in AI and machine learning. You can package vector search operations in functions and procedures to make them reusable across applications such as recommendation systems, similarity search, or semantic matching.
Create a vector table
CREATE TABLE prc_doc_pkg (
doc_id NUMBER PRIMARY KEY,
content CLOB,
embedding VECTOR(4)
);
INSERT INTO prc_doc_pkg VALUES (
1,
'Oracle 23ai introduces AI vector search for modern data applications.',
TO_VECTOR('[0.12, 0.95, -0.40, 0.60]')
);
INSERT INTO prc_doc_pkg VALUES (
2,
'PL/SQL packages in Oracle Database 23ai support functions and procedures.',
TO_VECTOR('[0.13, 0.90, -0.38, 0.62]')
);
COMMIT;
Create the vector search package
CREATE OR REPLACE PACKAGE prc_vector_pkg IS
FUNCTION get_similarity (
p_doc_id IN NUMBER,
p_query_vec IN VECTOR
) RETURN BINARY_DOUBLE;
PROCEDURE find_top_matches (
p_query_vec IN VECTOR,
p_limit IN NUMBER
);
END prc_vector_pkg;
/
CREATE OR REPLACE PACKAGE BODY prc_vector_pkg IS
FUNCTION get_similarity (
p_doc_id IN NUMBER,
p_query_vec IN VECTOR
) RETURN BINARY_DOUBLE IS
v_score BINARY_DOUBLE;
BEGIN
SELECT embedding <-> p_query_vec
INTO v_score
FROM prc_doc_pkg
WHERE doc_id = p_doc_id;
RETURN v_score;
END;
PROCEDURE find_top_matches (
p_query_vec IN VECTOR,
p_limit IN NUMBER
) IS
BEGIN
FOR rec IN (
SELECT doc_id, content, embedding <-> p_query_vec AS distance
FROM prc_doc_pkg
ORDER BY embedding <-> p_query_vec
FETCH FIRST p_limit ROWS ONLY
) LOOP
DBMS_OUTPUT.PUT_LINE('Doc ID: ' || rec.doc_id ||
' | Distance: ' || rec.distance ||
' | Content: ' || DBMS_LOB.SUBSTR(rec.content, 60));
END LOOP;
END;
END prc_vector_pkg;
/
Execute the vector package
SET SERVEROUTPUT ON;
-- Call function inside SQL
SELECT prc_vector_pkg.get_similarity(2, TO_VECTOR('[0.14, 0.92, -0.39, 0.61]')) AS sim_score
FROM dual;
-- Call procedure
BEGIN
prc_vector_pkg.find_top_matches(TO_VECTOR('[0.14, 0.92, -0.39, 0.61]'), 2);
END;
/
Sample Output:
SIM_SCORE --------- 0.02 Doc ID: 2 | Distance: 0.02 | Content: PL/SQL packages in Oracle Database 23ai support functions and procedures. Doc ID: 1 | Distance: 0.04 | Content: Oracle 23ai introduces AI vector search for modern data applications.
This example demonstrates how you can encapsulate vector similarity logic in packages and reuse it across your applications, which is valuable for AI and recommendation workloads.
Managing Packages
After creating packages, you may need to recompile them when dependent objects change or drop them when they are no longer needed. Oracle Database 23ai provides flexible management options.
-- Recompile package specification ALTER PACKAGE prc_employee_pkg COMPILE SPECIFICATION; -- Recompile package body ALTER PACKAGE prc_employee_pkg COMPILE BODY; -- Drop the entire package DROP PACKAGE prc_customer_pkg; -- Drop only the package body DROP PACKAGE BODY prc_vector_pkg;
Recompiling the body allows you to change internal logic without affecting applications that depend on the specification. If you modify the specification, dependent objects may need to be recompiled.
Conclusion
Packages in Oracle Database 23ai are essential for building robust, modular, and secure PL/SQL applications. They let you group related procedures and functions, manage business logic more effectively, and improve performance by storing compiled code in memory.
With Oracle 23ai’s support for JSON and vector data types, packages are more powerful than ever, enabling developers to integrate modern AI-driven features directly into the database. Mastering packages is a key step for any developer who wants to build enterprise-ready solutions on Oracle Database 23ai.
