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
- Easier Application Design
- Information Hiding
- Added Functionality
- Better Performance
Example: A Simple Package Specification Without a Body
CREATE PACKAGE trans_data AS -- bodiless package
TYPE TimeRec IS RECORD (
TYPE TransRec IS RECORD (
minimum_balance CONSTANT REAL := 10.00;
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
CREATE PACKAGE emp_bonus AS
PROCEDURE calc_bonus (date_hired employees.hire_date%TYPE);
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
DBMS_OUTPUT.PUT_LINE('Employees hired on ' || date_hired || ' get 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:
FUNCTION ABS (n NUMBER) RETURN NUMBER;
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!
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.
Package DBMS_ALERT lets you use database triggers to alert an application when specific database values change.
Package DBMS_OUTPUT enables you to display output from PL/SQL blocks, subprograms, packages, and triggers.
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.)
Packages HTF and HTP allow your PL/SQL programs to generate HTML tags.
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.
Package UTL_HTTP allows your PL/SQL programs to make hypertext transfer protocol (HTTP) callouts.
Package UTL_SMTP allows your PL/SQL programs to send electronic mails (emails) over Simple Mail Transfer Protocol (SMTP).