Oracle 9i Packages

This weeks tutorial explains about Oracle Packages, Developing Packages, Package Specification, Package Body; Overloading, Oracle Supplied Packages and Dbms_output.

Oracle Packages

A package is a group of procedures, functions, variables and SQL statements created as a single unit. It is used to store together related objects. A package has two parts, Package Specification or spec or package header and Package Body.

Package Specification acts as an interface to the package. Declaration of types, variables, constants, exceptions, cursors and subprograms is done in Package specifications. Package specification does not contain any code.

Package body is used to provide implementation for the subprograms, queries for the cursors declared in the package specification or spec.

Advantages:

• It allows you to group together related items, types and subprograms as a PL/SQL module.
• When a procedure in a package is called entire package is loaded, though it happens to be expensive first time the response is faster for subsequent calls.
• Package allows us to create types, variable and subprograms that are private or public

Package Syntax:

The package specification

The package body

If the specification of the package declares only types, constants, variables, exceptions, and call specs the package body is not required there. This type of packages only contains global variables that will be used by subprograms or cursors.

Example for a bodiless package

For accessing the types, subprograms etc from a package we write

Package_name.type_name

Package_name.subprogram_name

Executing Procedure and function from above package

The following package spec and package body declare and define 2 procedures. First the procedure is used to insert new records into the table DEPT, where the second procedure deletes a record from it.