Till so far we have seen most of the named PL/SQL blocks such as Database Cursors, PL/SQL Functions, Stored Procedure and Triggers now it’s time to move on to another most demanded PL/SQL tutorial on my YouTube channel which is PL/SQL Packages.

What are PL/SQL Packages in Oracle Database?

Packages are stored libraries in the database which allow us to group related PL/SQL objects under one name. Or in simple words, Packages are logical groups of related PL/SQL objects. Packages are named PL/SQL Blocks which mean they are permanently stored into the database schema and can be referenced or reused by your program.

Definition of PL/SQL Packages
Packages are stored libraries in the database which allow us to group related PL/SQL objects under one name.

What are the contents included in a package?

A package can hold multiple database objects such as

  • Stored Procedures
  • PL/SQL Functions
  • Database Cursors
  • Type declarations as well as
  • Variables

Info Byte
All the objects included into a package are collectively called Package Elements.

Package Architecture

PL/SQL package is divided into two parts:

  1. The Package Specification, also known as the Header and
  2. The Package Body

Both these parts are stored separately in the data dictionary. The package specification is the required part whereas the package body is optional, but it is a good practice to provide the body to the package.

Package Specification

Package specification is also known as the package header. It is the section where we put the declaration of all the package elements. Whatever elements we declare here in this section are publicly available and can be referenced outside of the package.

Info Byte
In this section we only declare package elements but we don’t define them. Also this is the mandatory section of the package.

Syntax of Package specification

CREATE OR REPALCE PACKAGE pkg_name IS
	Declaration of all the package element…;
END [pkg_name]; 

Package Body

In package body we provide the actual structure to all the package elements which we have already declared in the specification by programming them. Or we can say that a package body contains the implementation of the elements listed in the package specification.

Unlike package specification a package body can contain both declaration of the variable as well as the definition of all the package elements. Any package elements such as PL/SQL Function, a cursor or a stored procedure which is not in the package specification but coded in the package body is called Private Package Elements and thus they cannot be referenced outside the package.

Syntax of the package body

CREATE OR REPALCE PACKAGE BODY pkg_name IS
	Variable declaration;
	Type Declaration;
BEGIN
	Implementation of the package elements…
END [pkg_name];

You can refer to my YouTube video on the same topic for the detailed knowledge of both the above syntax.

That’s it for the introduction to PL/SQL Packages in Oracle Database. You can send me your Feedback, Suggestions or Questions on my Social Media or on the email. Click here for my Contact details. Thanks & have a great day!