6 Reasons Why You Should Use PL/SQL Packages

March 22, 2010

in performance, plsql

A package is:

…a database object that groups together logically related procedures/functions, and other constructs like variables, constants, PL/SQL types, cursors, and exceptions.

PL/SQL Packages One may well ask: when it’s possible to write standalone procedures/functions and define related variables within them, why have packages at all?

This article highlights six key reasons that make PL/SQL packages one of the most useful elements of PL/SQL programming. In a PL/SQL development environment, you simply cannot do without them.

  1. Organized code management Packages act like containers for related subprograms, types and items. Say you have several subprograms for inventory management (add_item, modify_item and so on) in your application – all of it can be bundled into a single inventory_mgmt package. This is of great help in application development as it makes code easy to locate, understand and reuse.

  2. Easy (top-down) application design A crucial feature of a package is the separation between its public interface (specification or specs), and its implementation (body). You can code and compile a package specification without its body. All you need for dependent programs to work is the interface information in the package specs. The package as well as the stored subprograms that reference it, will compile successfully. This means that you can develop the application in a planned, modular fashion and need not face bottlenecks due to incomplete implementation details. This is of special significance in top-down application development.

  3. Painless implementation changes Changes to a subprogram can create havoc with other programs that reference it. Not so with packages. If only code in the package body is changed, no change is needed in the dependent objects. Not even a recompile is required.

  4. Security and maintainability through private code With packages, you can specify which subprograms, types and items are public (visible and accessible outside the package) or private (hidden and inaccessible outside the package). For example, if a package contains four subprograms, three might be public and one private. The ability to have private portions in a package ensures that subprograms and other constructs that need not, or SHOULD not, be accessible publicly – remain hidden. This protects the integrity of the package, and also simplifies maintenance – any change to a private element impacts only this package and nothing else.

  5. New functionality: Session-wide persistence of public variables Packaged public variables and cursors persist for the duration of a session. They let you maintain data across transactions without storing it in the database. All subprograms that run in the environment share public package variables and cursors, and will read/edit the same values. This unique feature of PL/SQL packages can be used by applications for storing values with session-wide relevance, such as trace/debug options. (Caution: Judicious use is recommended for editing public variable values, as it can make the application messy and error-prone.)

  6. Better Performance When you invoke a packaged subprogram for the first time, the entire package is loaded into memory. Later calls to subprograms in the same package need no disk I/O. This translates to better performance.

Reasons enough to use PL/SQL packages as the default program unit for PL/SQL subprograms rather than standalone procedures and functions. Agree?

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post: