O'Reilly logo

Oracle PL/SQL Programming, 5th Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required