Packages in Oracle SQL | Package specification | Package body

Simple example of a package with a package specification and package body in Oracle SQL.

---

1. Create a Simple Package Specification

The package specification declares what is available to be called by other programs.

CREATE OR REPLACE PACKAGE simple_pkg AS
-- Declare a function
FUNCTION say_hello(name IN VARCHAR2) RETURN VARCHAR2;

-- Declare a procedure
PROCEDURE show_message;

END simple_pkg;
/

---

1. Create the Package Body

The package body contains the actual implementation of the function and procedure declared in the specification.

CREATE OR REPLACE PACKAGE BODY simple_pkg AS
-- Implement the function
FUNCTION say_hello(name IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN 'Hello, ' || name || '!';
END say_hello;

-- Implement the procedure
PROCEDURE show_message IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('This is a simple message from the package.');
END show_message;

END simple_pkg;
/

---

1. Test the Package

Example 1: Calling the Function

SET SERVEROUTPUT ON;

DECLARE
greeting VARCHAR2(50);
BEGIN
-- Call the function
greeting := simple_pkg.say_hello('John');
DBMS_OUTPUT.PUT_LINE(greeting);
END;
/

Expected Output:

Hello, John!

---

Example 2: Calling the Procedure

BEGIN
-- Call the procedure
simple_pkg.show_message;
END;
/

Expected Output:

This is a simple message from the package.

---

Explanation:

1. Package Specification:

Acts as the "public interface" of the package.

Declares the say_hello function and show_message procedure, which can be accessed externally.

1. Package Body:

Implements the logic of the declared function and procedure.

The function say_hello takes a name as input and returns a personalized greeting.

The procedure show_message simply prints a static message using DBMS_OUTPUT.PUT_LINE.

---