CHAPTER 8. Packages

PL/SQL User s Guide and Reference V2.3


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:







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,    ) ;

                                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




                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.



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


  1. Write packages as general as possible so that they can be used in future applications.
  2. Don t duplicate what already exists in Oracle.
  3. Design package specification before package body and package specification should contains types, objects and subprograms that are visible to users outside of package.
  4. To reduce the need for recompilation when code is changed, place as few items as possible in a package specification. Changes to a package body do not require Oracle to recompile dependent procedures. However, changes to package specification require Oracle to recompile every stored subprogram that references the package.