PL/SQL Packages

What is a PL/SQL Package?

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.

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.

What Goes in a PL/SQL Package?

• Get and Set methods for the package variables, if you want to avoid letting other subprograms 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 must change a query that is used in many places.
• Declarations for exceptions. Typically, you must be able to reference these from different subprograms, so that you can handle exceptions within invoked subprograms.
• Declarations for subprograms that invoke each other. You need not worry about compilation order for packaged subprograms, making them more convenient than standalone stored subprograms when they invoke back and forth to each other.
• Declarations for overloaded subprograms. You can create multiple variations of a subprogram, using the same names but different sets of parameters.
• Variables that you want to remain available between subprogram 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 subprograms, you must declare the type in a package so that both the invoking and invoked subprogram can refer to it.

Creating Package Specs:

CREATE PACKAGE package1 AS
PROCEDURE procedure1(n NUMBER);
END package1;

Creating Package Body:

CREATE OR REPLACE PACKAGE BODY PACKAGE1 AS
 PROCEDURE procedure1(n NUMBER) IS
  BEGIN
  DBMS_OUTPUT.PUT_LINE('Number entered is: ' || n);
  END procedure1;
END package1;
/

Referencing Package Contents:

SET SERVEROUTPUT ON;
BEGIN
package1.procedure1(3);
END;
/

Output:

anonymous block completed
Number entered is: 3

Private and Public Items in PL/SQL Packages

Items declared in the body of package are restricted to use within the package. PL/SQL code outside the package cannot reference the variable. Such items are called private.

CREATE OR REPLACE PACKAGE BODY emp_admin AS
   number_hired NUMBER;  -- visible only in this package

Items declared in the spec of package are visible outside the package. Any PL/SQL code can reference the item declared here. Such items are called public.

CREATE OR REPLACE PACKAGE emp_admin AS
-- Declare externally visible types, cursor, exception
   TYPE EmpRecTyp IS RECORD (emp_id NUMBER, sal NUMBER);
   CURSOR desc_salary RETURN EmpRecTyp;
   invalid_salary EXCEPTION;  -- Public variable

Overview of Product-Specific PL/SQL Packages

Various Oracle tools are supplied with product-specific packages that define application programming interfaces (APIs) that you can invoke from PL/SQL, SQL, Java, and other programming environments. This section briefly describes the following widely used product-specific packages:.

• DBMS_ALERT Package: DBMS_ALERT package lets you use database triggers to alert an application when specific database values change. The alerts are transaction based and asynchronous
• DBMS_OUTPUT Package: DBMS_OUTPUT package enables you to display output from PL/SQL blocks, subprograms, packages, and triggers. The package is especially useful for displaying PL/SQL debugging information. The procedure PUT_LINE outputs information to a buffer that can be read by another trigger, subprogram, or package. You display the information by invoking the procedure GET_LINE or by setting SERVEROUTPUT ON in SQL*Plus.
• DBMS_PIPE Package: DBMS_PIPE package allows different sessions to communicate over named pipes. (A pipe is an area of memory used by one process to pass information to another.) You can use the procedures PACK_MESSAGE and SEND_MESSAGE to pack a message into a pipe, then send it to another session in the same instance or to a waiting application such as a Linux or UNIX program.
• DBMS_CONNECTION_POOL Package: DBMS_CONNECTION_POOL package is meant for managing the Database Resident Connection Pool, which is shared by multiple middle-tier processes. The database administrator uses procedures in DBMS_CONNECTION_POOL to start and stop the database resident connection pool and to configure pool parameters such as size and time limit.
• HTF and HTP Packages: HTF and HTP packages enable your PL/SQL programs to generate HTML tags.
• UTL_FILE Package: UTL_FILE package lets PL/SQL programs read and write operating system text files. It provides a restricted version of standard operating system stream file I/O, including open, put, get, and close operations.
• UTL_HTTP Package: UTL_HTTP package enables your PL/SQL programs to make hypertext transfer protocol (HTTP) callouts. It can retrieve data from the Internet or invoke Oracle Web Server cartridges. The package has multiple entry points, each of which accepts a URL (uniform resource locator) string, contacts the specified site, and returns the requested data, which is usually in hypertext markup language (HTML) format.
• UTL_SMTP Package: UTL_SMTP package enables your PL/SQL programs to send electronic mails (eEmails) over Simple Mail Transfer Protocol (SMTP). The package provides interfaces to the SMTP commands for an eEmail client to dispatch eEmails to a SMTP server.

Next Article: PL/SQL Triggers

For any questions, suggestions or feedback, Please write to us. Thanks for Reading :)