PL/SQL Package - Components, Creation, and Best Practices

A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Packages usually have two parts, specification or spec and 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, you can use the SQL statement CREATE PACKAGE. A CREATE PACKAGE BODY statement defines the package body.

The package specification and the package body are the two fundamental components of a package. They can be described are as follows:

• Package Specification: The public interface of a package is the package specification. It contains the items that may be referenced from outside the package. The Construct PACKAGE statement is used to create a package specification.

• Package Body: All of the procedures and functions stated in the package specification, as well as any declarations of private types, variables, and cursors, are implemented in the package body. The Generate PACKAGE BODY statement is used to create a package body.

All public variables, cursors, objects, procedures, functions, and exceptions are declared in the package definition. Outside of the package, all the items that are declared in the specification can be accessed. A public element is one of several types of elements. As the package specification is a standalone component, it can occur without the package body. Whenever a package is referenced to, a session-specific instance of the package is produced. All package components that have begun in that instance are valid until the end of the session, once the instance is established for that session.

Example: A Simple Package Specification Without a Body

CREATE PACKAGE trans_data AS -- bodiless package
TYPE TimeRec IS RECORD (
minutes SMALLINT,
hours SMALLINT);
TYPE TransRec IS RECORD (
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;
/

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, then 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 the white space, the headers must match word for word; otherwise, PL/SQL raises an exception.

Example: Matching Package Specifications and Bodies

CREATE PACKAGE emp_bonus AS
PROCEDURE calc_bonus (date_hired employees.hire_date%TYPE);
END emp_bonus;
/
CREATE PACKAGE BODY emp_bonus AS
-- 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
BEGIN
DBMS_OUTPUT.PUT_LINE('Employees hired on ' || date_hired || ' get bonus.');
END;
END emp_bonus;
/

In PL/SQL, anytime a package is referenced to or called in a session, a new sample of that package is generated. Oracle’s Package Initialization feature allows you to initialiZe package components or execute any action at the time of instance creation. This is just an executing block that is placed in the package body after all of the package items have been defined. When a package is referred to for the first time in a session, this block will run.

Syntax:

CREATE [OR REPLACE] PACKAGE BODY 
IS


.
BEGINE
 
END 

The following are the reasons why you should use PL/SQL packages:

• Modularity: Packages in PL/SQL modules include various types, variables, constants, subprograms, cursors, and exceptions, which makes each package more reusable, controllable, readable, and trustworthy.
• Easier Application Design: You can code and compile requirements without the bodies of packages while developing an application. You can also use the packages to compile independent subprograms, making the application designing process easy.
• Information Hiding: Packages allow you to disclose your interface details in the package specification while keeping the implementation details hidden in the package body.
• Added Functionality: All subprograms running in the environment can access package public variables and cursors. Packages allow you to keep a track of the data between transactions without having to save it in the database.
• Better Performance: When you call a package subprogram for the first time, Oracle loads the package in the memory. Disk I/O is not required for future calls to other subprograms in the same package. This process aids in performance enhancement.

Oracle and various Oracle tools are supplied with product-specific packages that define application programming interfaces (APIs) that 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 that is 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 emails over simple mail transfer protocol (SMTP).