Introduction To PL/SQL Packages in Oracle Database

By Manish Sharma

PL/SQL Packages

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 publically available and can be referenced outside of the package.

 Info Byte

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

Syntax of Package specification


  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 programing 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

  Variable declaration;
  Type Declaration;
  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.

SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

NO SQL Script used in Video and in this article

Do Not Drink & Drive Use My Uber Code "UberRebellionRider"
SignUp using my referral code and get first ride worth $20 FREE

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn
  •          View Manish Sharma's profile on LinkedIn