DEV Community

Pranav Bakare
Pranav Bakare

Posted on

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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.


API Trace View

How I Cut 22.3 Seconds Off an API Call with Sentry

Struggling with slow API calls? Dan Mindru walks through how he used Sentry's new Trace View feature to shave off 22.3 seconds from an API call.

Get a practical walkthrough of how to identify bottlenecks, split tasks into multiple parallel tasks, identify slow AI model calls, and more.

Read more →

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay