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.

When working with package data, the developer needs to pay attention to the following points. 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.

To understand Oracle packages, we need to break them down into three key components:

1. Package:
   • Think of it as a container for related PL/SQL procedures, functions, variables, and data types. It's like a Lego set with various pieces that fit together to achieve specific functionalities within your database.
   • Packages promote code organization, modularity, and reusability.
   • They can be public (accessible to all) or private (only used within the database).
2. Package Specification:
   1. This is the public "face" of the package, similar to the instructions manual for your Lego set.
   2. It declares all the elements accessible from outside the package, including:
      • Type definitions: Custom data types used within the package.
      • Cursor declarations: Reusable SQL queries for data retrieval.
      • Variable and constant declarations: Shared variables and constants accessible across package procedures.
      • Exception declarations: Custom error messages for potential issues.
      • Subprogram specifications: "Signatures" of procedures and functions offered by the package, including names, parameters, and return types.
   3. The specification acts as a contract, ensuring users understand what the package offers and how to interact with it.
3. Package Body:
   • This is the "engine" of the package, containing the actual implementations of the declared elements. It's like the inner workings of your Lego set, hidden from plain sight but responsible for the actual functionality.
   • The body implements the logic for procedures, functions, and cursors declared in the specification.
   • Modifications to the body don't affect the interface defined in the specification, preserving external code stability.

Benefits of Using Oracle Packages:

• Organization and clarity: Code is organized into logical units, improving readability and maintainability.
• Reusability: Package elements can be easily reused across different parts of your database code.
• Data hiding and access control: Internal variables and procedures are protected from unauthorized access.
• Improved performance: Pre-compiled procedures and functions enhance execution speed.
• Simplified development: Focus on high-level functionalities without writing repetitive code.

In summary, Oracle packages offer a powerful and versatile way to structure your database code, leading to cleaner, more efficient, and secure applications. The next module describes how to control transactions and user security.