PL/SQL Package

Summary: in this tutorial, you will learn about PL/SQL package and the advantages of using the packages in your application development.

What is a PL/SQL package

In PL/SQL, a package is a schema object that contains definitions for a group of related functionalities. A package includes variables, constants, cursors, exceptions, procedures, functions, and subprograms. It is compiled and stored in the Oracle Database.

Typically, a package has a specification and a body. A package specification is mandatory while the package body can be required or optional, depending on the package specification.

The following picture illustrates PL/SQL packages:

pl/sql package

Package specification

The package specification declares the public objects that are accessible from outside the package.

If a package specification whose public objects include cursors and subprograms, then it must have a body which defines queries for the cursors and code for the subprograms.

Package body

A package body contains the implementation of the cursors or subprograms declared in the package specification. In the package body, you can declare or define private variables, cursors, etc., used only by package body itself.

A package body can have an initialization part whose statements initialize variables or perform other one-time setups for the whole package.

A package body can also have an exception-handling part used to handle exceptions.

Why using PL/SQL packages

The package is a powerful feature of PL/SQL that you should use it in any project. The following are the advantages of the package:

Make code more modular

Packages allow you to encapsulate logically related types, variables, constants, subprograms, cursors, and exceptions in named PL/SQL modules. By doing this, you make each package more reusable, manageable, readable and reliable.

Hide implementation details

Packages allow you to expose the functionality via their specifications and hide the detailed implementation in the package body.

It means that you can enhance the code in the body of the package without affecting other dependent packages or applications.

Improve application performance

Oracle loads the package into memory at the first time you invoke a package subprogram. The subsequent calls of other subprograms in the same package do not require disk I/O. This mechanism helps improve performance.

Minimize unnecessary recompiling code

Packages help avoid the unnecessary recompiling process. For instance, if you change the body of a package function, Oracle does not recompile the subprograms that use the function, because the subprograms are only dependent on the package specification, not the package body.

Manage authorization easily

By encapsulate objects in a package, you grant role on the package, instead of granting roles on each object in the package.

PL/SQL package concept is simple but powerful. They allow you to encapsulate the code and make your application easier to develop and maintain.

Was this tutorial helpful?