Oracle PL/SQL Package: Type, Specification, Body [Example]

Details

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.

In this tutorial, you will learn-

Components of Packages

PL/SQL package has two components.

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.

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.

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>

Now we are going to see how to refer package elements in the program.

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.

Syntax

Packages in PL/SQL 

CREATE [OR REPLACE] PACKAGE BODY <package_name>
IS
<Private element definition>
<sub_program and public element definition>
.
BEGINE
<Package Initialization> 
END <package_name>

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.

Packages in PL/SQL

Syntax: 

CREATE [OR REPLACE] PACKAGE BODY <package_name>
IS
<Private element declaration>
.
.
.
<Public element definition that refer the above private element>
.
.
<Private element definition> 
.
BEGIN
<package_initialization code>; 
END <package_name>

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

Packages in PL/SQL 

CREATE OR REPLACE PACKAGE gtupapers_get_set
IS
PROCEDURE set_record (p_emp_rec IN emp%ROWTYPE);
FUNCTION get record (p_emp no IN NUMBER) RETURN emp%ROWTYPE;
END gtupapers_get_set:
/

Output: 

Package created

Code Explanation

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

Packages in PL/SQL 

CREATE OR REPLACE PACKAGE BODY gtupapers_get_set
IS	
PROCEDURE set_record(p_emp_rec IN emp%ROWTYPE)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO emp
VALUES(p_emp_rec.emp_name,p_emp_rec.emp_no; p_emp_rec.salary,p_emp_rec.manager);
COMMIT;
END set_record;
FUNCTION get_record(p_emp_no IN NUMBER)
RETURN emp%ROWTYPE
IS
l_emp_rec emp%ROWTYPE;
BEGIN
SELECT * INTO l_emp_rec FROM emp where emp_no=p_emp_no
RETURN l_emp_rec;
END get_record;
BEGUN	
dbms_output.put_line(‘Control is now executing the package initialization part');
END gtupapers_get_set:
/

Output: 

Package body created

Code Explanation

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

Packages in PL/SQL 

DECLARE
l_emp_rec emp%ROWTYPE;
l_get_rec emp%ROWTYPE;
BEGIN
dbms output.put line(‘Insert new record for employee 1004');
l_emp_rec.emp_no:=l004;
l_emp_rec.emp_name:='CCC';
l_emp_rec.salary~20000;
l_emp_rec.manager:=’BBB’;
gtupapers_get_set.set_record(1_emp_rec);
dbms_output.put_line(‘Record inserted');
dbms output.put line(‘Calling get function to display the inserted record'):
l_get_rec:=gtupapers_get_set.get_record(1004);
dbms_output.put_line(‘Employee name: ‘||l_get_rec.emp_name);
dbms_output.put_line(‘Employee number:‘||l_get_rec.emp_no);
dbms_output.put_line(‘Employee salary:‘||l_get_rec.salary');
dbms output.put line(‘Employee manager:‘||1_get_rec.manager);		
END:
/

Output: 

Insert new record for employee 1004
Control is now executing the package initialization part
Record inserted
Calling get function to display the inserted record
Employee name: CCC
Employee number: 1004
Employee salary: 20000
Employee manager: BBB

Code Explanation:

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.

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 package like object_id, creation_date, last_ddl_time, etc. It will contain the objects created by all users. SELECT * FROM all_objects where object_name ='<package_name>'
USER_OBJECT Gives the details of the package like object_id, creation_date, last_ddl_time, etc. It will contain the objects created by the current user. SELECT * FROM user_objects where object_name ='<package_name>'
ALL_SOURCE Gives the source of the objects created by all users. SELECT * FROM all_source where name='<package_name>'
USER_SOURCE Gives the source of the objects created by the current user. SELECT * FROM user_source where name='<package_name>'
ALL_PROCEDURES Gives the subprogram details like object_id, overload details, etc created by all users. SELECT * FROM all_procedures Where object_name='<package_name>'
USER_PROCEDURES Gives the subprogram details like object_id, overload details, etc. created by the current user. SELECT * FROM user_procedures Where object_name='<package_name>'

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 write 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.

 

YOU MIGHT LIKE: