Oracle PL/SQL Packages

Yusuf SEZER October 11, 2020 ORACLE

This article contains information about Oracle PL/SQL Packages, create , execute and drop packages and also predefined packages.

What is Package in Oracle PL/SQL?

Packages are a structure in which structures such as procedures and functions are gathered together.

Oracle packages definition consists of two parts: definition and body.

Create PACKAGE in Oracle PL/SQL

The keywords to be used when creating the package are as follows.

CREATE [OR REPLACE] PACKAGE <PACKAGE_NAME> IS | AS
    -- definitions
END [<PACKAGE_NAME>];

CREATE [OR REPLACE] PACKAGE <PACKAGE_NAME> IS | AS

-- definitions

END [<PACKAGE_NAME>];

An example of creating a package with Oracle PL / SQL is as follows.

CREATE OR REPLACE PACKAGE SAMPLE_PACKAGE
IS
    PROCEDURE MyLOOP(Start IN PLS_INTEGER, Finish IN PLS_INTEGER);
    FUNCTION CONVERT_FIRST_LETTER_TO_UPPERCASE (MYTEXT VARCHAR2) RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE SAMPLE_PACKAGE

PROCEDURE MyLOOP(Start IN PLS_INTEGER, Finish IN PLS_INTEGER);

FUNCTION CONVERT_FIRST_LETTER_TO_UPPERCASE (MYTEXT VARCHAR2) RETURN VARCHAR2;

END;

After making the necessary definitions for the package, the content of the structures such as procedure and function is written in the BODY section.

CREATE OR REPLACE PACKAGE BODY SAMPLE_PACKAGE
IS
    -- MyLOOP
    PROCEDURE MyLOOP (
        START IN PLS_INTEGER,
        FINISH IN PLS_INTEGER
    ) AS
    BEGIN
        FOR v_number IN START .. FINISH LOOP
            DBMS_OUTPUT.put_line('Command executed ' ||v_number || ' times.');
        END LOOP;
    END MyLOOP;
    
    -- CONVERT_FIRST_LETTER_TO_UPPERCASE
    FUNCTION CONVERT_FIRST_LETTER_TO_UPPERCASE (MYTEXT IN VARCHAR2)
        RETURN VARCHAR2
    AS
    BEGIN
        RETURN UPPER(SUBSTR(MYTEXT, 1, 1)) || LOWER(SUBSTR(MYTEXT, 2));
    END CONVERT_FIRST_LETTER_TO_UPPERCASE;
END;

CREATE OR REPLACE PACKAGE BODY SAMPLE_PACKAGE

-- MyLOOP

PROCEDURE MyLOOP (

START IN PLS_INTEGER,

FINISH IN PLS_INTEGER

) AS

BEGIN

FOR v_number IN START .. FINISH LOOP

DBMS_OUTPUT.put_line('Command executed ' ||v_number || ' times.');

END LOOP;

END MyLOOP;

-- CONVERT_FIRST_LETTER_TO_UPPERCASE

FUNCTION CONVERT_FIRST_LETTER_TO_UPPERCASE (MYTEXT IN VARCHAR2)

RETURN VARCHAR2

BEGIN

RETURN UPPER(SUBSTR(MYTEXT, 1, 1)) || LOWER(SUBSTR(MYTEXT, 2));

END CONVERT_FIRST_LETTER_TO_UPPERCASE;

END;

Execute PACKAGE in Oracle PL/SQL

We can access the package contents as follows.

PACKAGE_NAME.PACKAGE_CONTENT

1	PACKAGE_NAME.PACKAGE_CONTENT

For example, let’s run the MyLOOP procedure above.

SET SERVEROUTPUT ON;
EXECUTE SAMPLE_PACKAGE.MyLOOP(1, 10);

1 2	SET SERVEROUTPUT ON; EXECUTE SAMPLE_PACKAGE.MyLOOP(1, 10);

Similarly, other objects in the package can be accessed.

The user_objects table is used to get information about the created packages.

SELECT * FROM user_objects WHERE object_type = 'PACKAGE';

1	SELECT * FROM user_objects WHERE object_type = 'PACKAGE';

The DESC keyword is used to get information about a package.

DESC PACKAGE_NAME;

1	DESC PACKAGE_NAME;

DROP PACKAGE in Oracle PL/SQL

The DROP keyword is used to delete the package.

DROP PACKAGE PACKAGE_NAME;

1	DROP PACKAGE PACKAGE_NAME;

Predefined Oracle PL/SQL PACKAGES

There are packages created for various processes within Oracle.

DBMS_OUTPUT in Oracle

This is the package used to display the data on the screen. You can find detailed information in the below link.

https://docs.oracle.com/database/121/ARPLS/d_output.htm#ARPLS036

DBMS_PIPE in Oracle

This is the package used to process between sessions. You can find detailed information in the below link.

https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_pipe.htm#CHDDFCFC

DBMS_LOCK in Oracle

This is the package in which user lock operations are performed. You can find detailed information in the below link.

https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lock.htm#ARPLS021

DBMS_STANDART in Oracle

This is the standard package used in PL/SQL commands.

For example; The RAISE_APPLICATION_ERROR function is included in the DBMS_STANDART package.

The reason for using the packages, as can be understood from the examples, is the collection of commands that do interrelated work.

The use of packages for commands where the same or interrelated operations are performed will be useful for regular development.

You can find more detailed information about below topics in the below link.

PL/SQL Tutorial

You will find below topics in this article.

You can find more information about packages at docs.oracle.com

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux