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.
What Goes In a PL/SQL Package?
The following is contained in a PL/SQL package:
Advantages of PL/SQL Packages
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 Package STANDARD Defines the PL/SQL Environment
A package named STANDARD defines the PL/SQL environment. The package spec globally declares types, exceptions, and subprograms, which are available automatically to PL/SQL programs. For example, 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.
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).Previous Next
Download Interview Questions asked by top MNCs in 2019?