CHAPTER 8. Packages
8.1 Introduction
What
is a package: A package is a database object that group related type
definitions, objects, subprograms together
Two
parts of a package: A package consists of two parts; the package specification
part and package body part.
Specification
part: The package specification part declares the constants, variables, types,
exceptions and cursor and subprogram callable from outside the package.
The
body of the package is the implementation of a package. It defines cursors, and
subprograms. Subprograms not declared in the package specification part are
only available within the package.
Syntax
of creating a Package:
-
- To create a package specification, use the following syntax:
CREATE
PACKAGE name AS --
specification ( object, types, subs visible outside )
-
- public type and object declarations.
-
- subprogram specificatons
-
- Example of public cursor type:
-
- TYPE
partType IS RECORD ( ) ; CURSOR
c RETURN partType IS select
END [name] ;
-
- To create a package body, use the following syntax:
CREATE
PACKAGE BODY name AS -- body ( hidden
from the users )
-
- private type and object specifications (variables, constants, exceptions,
cursors)
-
- subprogram definitions
[ BEGIN
-
- Initialization statements ]
END [ name ] ;
8.2 Advantages
of Package
The advantages of using package include
modularity, easier application design, information hiding, added functionality,
and better performance.
Modularity: Related functionality can be gathered
and stored together just like C library functions.
Easier
Application Design: package
specification can be created without the implementation of the
package body.
Information
Hiding: The subprogram
implementation, private data types, cursors,
Added
Functionality: Packaged public
variables and cursors persists for the duration of a session.
They
can be shared among all subprograms. The global variable also allows you to
maintain data across transactions without having to stored it in the database.
Better
Performance: Once a packaged
subprogram is called for the first time, it may remain in the
memory for a while. That is, the
subsequent call to the subprogram or related subprograms may require no disk
I/O. When a standalone subprogram is modified, Oracle must recompile all stored
programs that call the subprogram. However, if you change the definition of a
packaged function, Oracle need not recompile the calling subprograms because
they do not depend on the package body.
8.3 Package
Specification
Package
specification contains public declarations. Those declarations are local to
your database schema and global to the package. That is, the declarations in
the package specification is accessible from you application and from anywhere
in the package.
A package
specification may contain only type, variable, exception and constant
declarations, the package body is not necessary since only cursor and
subprograms need implementations.
To refer the
type, objects and subprogram, use the dot notation:
package_name.type_name
package_name.object_name
package_name.subprogram_name
To call a
packaged procedure from SQL/PLUS,
SQL> EXECUTE packagename.procedure_name
To call
procedure from a anonymous PL/SQL block embedded in a Pro*C program:
EXEC SQL EXECUTE
BEGIN
Packagename.procedure_name
( :host_var1, :host_var2, ) ;
END ;
END-EXEC ;
You can no
reference remote packaged variable directly or indirectly. That is, if a public
variable is referenced in a function/procedure, that function or procedure
cannot be called remotely.
8.4 Package
Body
Package body
mainly contains the private type and object declarations and subprogram bodies.
If the subprogram is declared in the package specification and defined in
package body, the heading of subprogram must match word for word except white
spaces. That is the following will cause a problem:
CREATE PACKAGE tmp AS
PROCEDURE p ( I
sp.qty%TYPE ) ; -- qty is of type integer
END [tmp ] ;
CREATE PACKAGE BODY tmp AS
PROCEDURE P ( I INTEGER ) ; -- It does not match I s type even both I are
integer
BEGIN
END ;
The
initialization part of package body is executed only once. It initializes local
and global variables defined in package specification and package body.
The subprograms
in package can be overloaded.
8.5 System
Supplied Packages
Oracle 7 and
various Oracle tools are supplied with product-specific packages that help you
build PL/SQL based applications.
1.
Package
STANDARD
The
standard package contain all the Oracle functions and procedures such as
TO_CHAR, ABS and etc. When the functions in standard package are called, the
package name STANDARD is not necessary unless the function or procedure in
STANDARD package is overloaded.
2.
DBMS_STANDARD
Package
DBMS_STANDARD provides language facilities that help your application
interacted with Oracle. Raise_application_error is one of procedure provide in
DBMS_STANDARD package.
3.
DBMS_SQL
The
package allows PL/SQL to execute data definition, data manipulation and data
retrieval statement dynamically at run time The data definition statements and
dynamic SQL is not allowed in PL/SQL.
4.
DBMS_ALERT
The
package allows you to use database triggers to alter an application when
certain database table column changes. The alters are transaction based and
asynchronous.
5.
DBMS_OUTPUT
The
package allows user to display the result on the screen in PL/SQL block or
subprograms so that users can debug the PL/SQL code.
6.
DBMS_PIPE
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.) The
procedure PACK_MESSAGE AND SEND_MESSAGE will pack a message into a pipe and
sends the message to another session in the same instance.
To
receive message, use RECEIVE_MESSAGE AND UNPACK_MESSAGE. These two procedures
receive and unpack the message. Pipes
can be used in many way, for example, you can write routines in C that allow
external servers to collect information, then send it through pipes to
procedures stored in an Oracle database.
7.
UTL_FILE
Package
UTL_FILE allows your PL/SQL programs to read and write OS text files. It
provides a restricted version of standard OS stream file I/O, including open,
put, get and close operation. The procedures in this package include fopen,
put_line, get_line and etc.
PL/SQL
file I/O is available on both the client and server sides. However, on the
server side, file access is restricted to those directories explicitly listed
in the accessible directories list, which is stored in Oracle initialization
file.
8.6 Guidelines for Design Packages