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:
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.
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.
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.
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.
Take advantage of persistent package data to improve the response time of your application by caching (and not requerying) static ...