Oracle PL/SQL Programming, 5th Edition

When to Use Packages

By now, I’ve covered the rules, syntax, and nuances of constructing packages. Let’s now return to the list of reasons you might want to use PL/SQL packages and explore them in more detail. These scenarios include:

Encapsulate (hide) data manipulation

Rather than have developers write SQL statements (leading to inefficient variations and maintenance nightmares), provide an interface to those SQL statements. This interface is known as a table API or transaction API.

Avoid the hardcoding of literals

Use a package with constants to give a name to the literal (“magic”) value and avoid hardcoding it into individual (and multiple) programs. You can, of course, declare constants within procedures and functions as well. The advantage of a constant defined in a package specification is that it can be referenced outside of the package.

Improve the usability of built-in features

Some of Oracle’s own utilities, such as UTL_FILE and DBMS_OUTPUT, leave lots to be desired. Build your own package on top of Oracle’s to correct as many of the problems as possible.

Group together logically related functionality

If you have a dozen procedures and functions that all revolve around a particular aspect of your application, put them all into a package so that you can manage (and find) that code more easily.

Cache session-static data to improve application performance

Take advantage of persistent package data to improve the response time of your application by caching (and not requerying) static ...