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 ( 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; /
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); 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; /
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.
- 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).