CHAPTER 8. Packages
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
- - 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
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:
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
Packagename.procedure_name ( :host_var1, :host_var2, ) ;
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
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.
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.
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.
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.
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.
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.
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