Oracle PL/SQL Package: Type, Specification, Body

What is Package in Oracle?

PL/SQL package is a logical grouping of a related subprogram (procedure/function) into a single

element. A Package is compiled and stored as a database object that can be used later.

Components of Packages

PL/SQL package has two components.

 Package Specification
 Package Body

Package Specification

Package specification consists of a declaration of all the public variables, cursors, objects,
procedures, functions, and exception.

Below are few characteristics of the Package specification.

 The elements which are all declared in the specification can be accessed from outside of
the package. Such elements are known as a public element.
 The package specification is a standalone element that means it can exist alone without
package body.
 Whenever a package has referred an instance of the package is created for that particular
session.
 After the instance is created for a session, all the package elements that are initiated in
that instance are valid until the end of the session.

Syntax

CREATE [OR REPLACE] PACKAGE <package_name>

IS
<sub_program and public element declaration>
.
.
END <package name>

The above syntax shows the creation of package specification.

Package Body

It consists of the definition of all the elements that are present in the package specification. It can
also have a definition of elements that are not declared in the specification; these elements are
called private elements and can be called only from inside the package.

Below are characteristics of a package body.

 It should contain definitions for all the subprograms/cursors that have been declared in
the specification.
 It can also have more subprograms or other elements that are not declared in
specification. These are called private elements.
 It is a dependable object, and it depends on package specification.
 The state of the package body becomes 'Invalid' whenever the specification is compiled.
Therefore, it needs to be recompiled each time after the compilation of specification.
 The private elements should be defined first before they are used in the package body.
 The first part of the package is the global declaration part. This includes variables,
cursors and private elements (forward declaration) that is visible to the entire package.
 The last part of the package is Package initialization part that executes one time whenever
a package is referred first time in the session.

Syntax:

CREATE [OR REPLACE] PACKAGE BODY <package_name>

IS
<global_declaration part>
<Private element definition>
<sub_program and public element definition>
.
<Package Initialization>
END <package_name>

 The above syntax shows the creation of package body.

Referring Package Elements

Once the elements are declared and defined in the package, we need to refer the elements to use
them.

All the public elements of the package can be referred by calling the package name followed by
the element name separated by period i.e. '<package_name>.<element_name>'.

The public variable of the package can also be used in the same way to assign and fetch values
from them i.e. '<package_name>.<variable_name>'.
Create Package in PL/SQL

In PL/SQL whenever a package is referred/called in a session a new instance will be created for
that package.

Oracle provides a facility to initialize package elements or to perform any activity at the time of
this instance creation through 'Package Initialization'.

This is nothing but an execution block that is written in the package body after defining all the
package elements. This block will be executed whenever a package is referred for the first time
in the session.

 The above syntax shows the definition of package initialization in the package body.

Forward Declarations

Forward declaration/reference in the package is nothing but declaring the private elements
separately and defining it in the later part of the package body.

Private elements can be referred only if it is already declared in the package body. For this
reason, forward declaration is used. But it is rather unusual to use because in most of the time
private elements are declared and defined in the first part of the package body.

Forward declaration is an option provided by Oracle, it is not mandatory and using and not using
is up to programmer's requirement.
The above syntax shows forward declaration. The private elements are declared separately in the
forward part of the package, and they have been defined in the later part.

Cursors Usage in Package

Unlike other Elements one needs to be careful in using cursors inside the package.

If the cursor is defined in the package specification or in global part of the package body, then
the cursor once opened will persist till the end of the session.

So one should always use the cursor attributes '%ISOPEN' to verify the state of the cursor before
referring it.

Overloading

Overloading is the concept of having many subprograms with the same name. These
subprograms will be differing from each other by a number of parameters or types of parameters
or return type i.e. subprogram with the same name but with different number of parameters,
different type of parameters or different retype are considered as overloading.

This is useful when many subprograms needs to do the same task, but the way of calling each of
them should be different. In this case, the subprogram name will be kept same for all and the
parameters will be changed as per calling statement.

Example 1: In this example, we are going to create a package to get and set the values of
employee's information in 'emp' table. The get_record function will return the record type output
for the given employee number, and set_record procedure will insert the record type record into
the emp table.

Step 1) Package Specification Creation

Code Explanation

 Code line 1-5: Creating the package specification for guru99_get_set with one procedure
and one function. These two are now public elements of this package.

Step 2) Package contains Package body, where all procedures and functions actual definition
will be defined. In this step, Package Body is created.
Code Explanation

 Code line 7: Creating the package body.

 Code line 9-16: Defining the element 'set_record' that is declared in the specification.
This is same as defining the standalone procedure in PL/SQL.
 Code line 17-24: Defining the element 'get_record'. It is same as defining the standalone
function.
 Code line 25-26: Defining the package initialization part.

Step 3) Creating an anonymous block to insert and display the records by referring to the above
created package.

Code Explanation:

 Code line 34-37: Populating the data for record type variable in an anonymous block to
call 'set_record' element of the package.
 Code line 38: Call has been made to 'set_record' of guru99_get_set package. Now the
package is instantiated and it will persist until the end of the session.
  The package initialization part is executed since this is the first call to the package.
 The record in inserted by the 'set_record' element into the table.
 Code line 41: Calling the 'get_record' element to display the details of the inserted
employee.
 The package is referred for the second time during the 'get_record' call to the package.
But the initialization part is not executed this time as the package is already initialized in
this session.
 Code line 42-45: Printing the employee details.

Dependency in Packages

Since the package is the logical grouping of related things, it has some dependencies. Following
are the dependency that is to be taken care.

 A Specification is a standalone object.

 A Package body is dependent on specification.
 Package body can be compiled separately. Whenever specification is compiled, the body
needs to be recompiled as it will become invalid.
 The subprogram in package body that is dependent on a private element should be
defined only after the private element declaration.
 The database objects that are referred in the specification and body needs to be in valid
status at the time of package compilation.

Package Information

Once the package information is created, the package information such as package source,
subprogram details, and overload details are available in the Oracle data definition tables.

Below table gives the data definition table and the package information that is available in the
table.

Table Name Description Query

ALL_OBJECT Gives the details of the SELECT * FROM all_objects where

package like object_id, object_name ='<package_name>'
creation_date, last_ddl_time,
etc. It will contain the objects
created by all users.

USER_OBJECT Gives the details of the SELECT * FROM user_objects

package like object_id, where object_name
creation_date, last_ddl_time, ='<package_name>'
etc. It will contain the objects
created by the current user.
 ALL_SOURCE Gives the source of the SELECT * FROM all_source where
objects created by all users. name='<package_name>'

USER_SOURCE Gives the source of the SELECT * FROM user_source where

objects created by the current name='<package_name>'
user.

ALL_PROCEDURES Gives the subprogram details SELECT * FROM all_procedures

like object_id, overload Where
details, etc created by all object_name='<package_name>'
users.

USER_PROCEDURES Gives the subprogram details SELECT * FROM user_procedures

like object_id, overload Where
details, etc. created by the object_name='<package_name>'
current user.

UTL FILE – An Overview

UTL File is the separate utility package provided by Oracle to perform special tasks. This is
mainly used for reading and writes the operating system files from PL/SQL packages or
subprograms. It got the separate functions to put the information and to get the information from
files. It also allows to read/write in the native character set.

The Programmer can use this to write operating system files of any type and the file will be
written directly to the database server. The name and directory path will be mentioned at the time
writing.

Summary

We have now learned the packages in PL/SQL, and you should be now able to work in the
following.