Lesson 7

This module examined the advantages, disadvantages, and common uses of PL/SQL packages.
The two separate SQL commands required to create packages were defined and practiced. Finally, you tested a package by calling the procedures and functions that are stored inside the package.
In this module, you learned how to:

1. Define a package and its components
2. Describe appropriate uses of a package
3. Build the outer shell of a package
4. Build the package body
5. Create a PL/SQL block that calls different parts of a package

The specification of a package declares the public types, variables, constants, and subprograms that are visible outside the immediate scope of the package. The body of a package defines the objects declared in the specification, as well as private objects that are not visible to applications outside the package. Oracle stores the specification and body of a package separately in the database. Other schema objects that call or reference public program objects depend only on the package specification, not on the package body. Using this distinction, you can change the definition of a program object in the package body without causing Oracle to invalidate other schema objects that call or reference the program object. Oracle invalidates dependent schema objects only if you change the declaration of the program object in the package specification.

When writing packages, keep them general so they can be reused in future applications. Become familiar with the packages that Oracle supplies, and avoid writing packages that duplicate features already provided by Oracle. Design and define package specs before the package bodies. Place in a specification only those things that must be visible to invoking programs. That way, other developers cannot build unsafe dependencies on your implementation details. To reduce the need for recompiling when code is changed, place as few items as possible in a package specification. Changes to a package body do not require recompiling invoking subprograms. Changes to a package specification require the database to recompile every stored subprogram that references the package.

Package data consists of variables and constants that are defined at the package level, that is, not within a particular function or procedure in the package. The scope of the package data is therefore not a single program, but rather the package as a whole. In the PL/SQL runtime architecture, package data structures persist (hold their values) for the duration of a session (rather than the duration of execution for a particular program).
If package data is declared inside the package body, then that data persists for thesession but can be accessed only by elements defined in the package itself (private data). If package data is declared inside the package specification, then that data persists for the session and is directly accessible (to both read and modify the value) by any program that has EXECUTE authority on that package (public data). Public package data is very similar to and potentially as dangerous as GLOBAL variables in Oracle Forms. If a packaged procedure opens a cursor, that cursor remains open and is available throughout the session. It is not necessary to define the cursor in each program. One module can open a cursor while another performs the fetch. Additionally, package variables can carry data across the boundaries of transactions because they are tied to the session rather than to a single transaction.

This module introduced the following glossary terms:

1. Package
2. Package specification
3. Package body

The next module describes how to control transactions and user security.