What is PL/SQL Packages?

A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Packages usually have two parts, a specification (spec) and a body; sometimes the body is unnecessary. The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. The body defines the queries for the cursors and the code for the subprograms.
To create package specs, use the SQL statement CREATE PACKAGE. A CREATE PACKAGE BODY statement defines the package body.

Enroll yourself in PL/SQL Training and give a head-start to your career in PL?SQL!

What Goes In a PL/SQL Package?

The following is contained in a PL/SQL package:

  • Get and Set methods for the package variables, if you want to avoid letting other procedures read and write them directly.
  • Cursor declarations with the text of SQL queries. Reusing exactly the same query text in multiple locations is faster than retyping the same query each time with slight differences. It is also easier to maintain if you need to change a query that is used in many places.
  • Declarations for exceptions. Typically, you need to be able to reference these from different procedures, so that you can handle exceptions within called subprograms.
  • Declarations for procedures and functions that call each other. You do not need to worry about compilation order for packaged procedures and functions, making them more convenient than standalone stored procedures and functions when they call back and forth to each other.
  • Declarations for overloaded procedures and functions. You can create multiple variations of a procedure or function, using the same names but different sets of parameters.
  • Variables that you want to remain available between procedure calls in the same session. You can treat variables in a package like global variables.
  • Type declarations for PL/SQL collection types. To pass a collection as a parameter between stored procedures or functions, you must declare the type in a package so that both the calling and called subprogram can refer to it.

Come to Intellipaat’s PL/SQL Community if you have more queries on PL/SQL!

Advantages of PL/SQL Packages

  • Modularity
  • Easier Application Design
  • Information Hiding
  • Added Functionality
  • Better Performance

package scope

Example: A Simple Package Specification Without a Body

CREATE PACKAGE trans_data AS -- bodiless package
minutes SMALLINT,
hours SMALLINT);
category VARCHAR2(10),
account INT,
amount REAL,
time_of TimeRec);
minimum_balance CONSTANT REAL := 10.00;
number_processed INT;
insufficient_funds EXCEPTION;
END trans_data;

Learn Oracle PL SQL

The Package Body

The package body contains the implementation of every cursor and subprogram declared in the package spec. Subprograms defined in a package body are accessible outside the package only if their specs also appear in the package spec. If a subprogram spec is not included in the package spec, that subprogram can only be called by other subprograms in the same package.
A package body must be in the same schema as the package spec. To match subprogram specs and bodies, PL/SQL does a token-by-token comparison of their headers. Except for white space, the headers must match word for word. Otherwise, PL/SQL raises an exception.
Example: Matching Package Specifications and Bodies

PROCEDURE calc_bonus (date_hired employees.hire_date%TYPE);
END emp_bonus;
-- the following parameter declaration raises an exception
-- because 'DATE' does not match employees.hire_date%TYPE
-- PROCEDURE calc_bonus (date_hired DATE) IS
-- the following is correct because there is an exact match
PROCEDURE calc_bonus (date_hired employees.hire_date%TYPE) IS
DBMS_OUTPUT.PUT_LINE('Employees hired on ' || date_hired || ' get bonus.');
END emp_bonus;

How to Package STANDARD Defines the PL/SQL Environment

A package named STANDARD defines the PL/SQL environment. The package spec globally declares types, exceptions, and PL/SQL subprograms, which are available automatically to PL/SQL programs. For example, the package STANDARD declares function ABS, which returns the absolute value of its argument, as follows:


The contents of package STANDARD are directly visible to applications. You do not need to qualify references to its contents by prefixing the package name.

Get familiar with the top PL/SQL Interview Questions to get a head start in your career!

Product-Specific Packages

Oracle and various Oracle tools are supplied with product-specific packages that define application programming interfaces (APIs) you can call from PL/SQL, SQL, Java, or other programming environments.

  • DBMS_ALERT Package

Package DBMS_ALERT lets you use database triggers to alert an application when specific database values change.

  • DBMS_OUTPUT Package

Package DBMS_OUTPUT enables you to display output from PL/SQL blocks, subprograms, packages, and triggers.

  • DBMS_PIPE Package

Package DBMS_PIPE allows different sessions to communicate over named pipes. (A pipe is an area of memory used by one process to pass information to another.)

  • HTF and HTP Packages

Packages HTF and HTP allow your PL/SQL programs to generate HTML tags.

  • UTL_FILE Package

Package UTL_FILE lets PL/SQL programs read and write operating system (OS) text files. It provides a restricted version of standard OS stream file I/O, including open, put, get, and close operations.

  • UTL_HTTP Package

Package UTL_HTTP allows your PL/SQL programs to make hypertext transfer protocol (HTTP) callouts.

  • UTL_SMTP Package

Package UTL_SMTP allows your PL/SQL programs to send electronic mails (emails) over Simple Mail Transfer Protocol (SMTP).

Course Schedule

Name Date
SQL Training 2022-01-15 2022-01-16
(Sat-Sun) Weekend batch
View Details
SQL Training 2022-01-22 2022-01-23
(Sat-Sun) Weekend batch
View Details
SQL Training 2022-01-29 2022-01-30
(Sat-Sun) Weekend batch
View Details

Leave a Reply

Your email address will not be published. Required fields are marked *