Received this request via email today:
So I checked, and quickly found and reported back:
Hi Steven, in our shop we have an ongoing debate regarding when to use packages and when to create a collection of procedures/functions. We are not aligned at all, some people here tend to ignore packages for some strange reasons, I would like to nudge them in the right direction, so that where you come into play.
One of my co workers tried to look into a couple of you books to your argumentation for use of packages, and could not, to her astonishment, see any recommendations from you regarding this, can you point us in the right direction, surely you must have debated this issue somewhere.This came as a bit of a surprise to me (inability to find recommendations from me on packages).
So I checked, and quickly found and reported back:
In my humongous 6th edition Oracle PL/SQL Programming, there is a whole chapter on packages (18) and on 651 I offer “Why Packages?”.
In my Best Practices book I have a whole chapter on Packages and on page 207 I make the argument to ALWAYS use packages, not schema level subprograms.
In case, however, you don't have the books handy, here are my high-level thoughts on packages.
First, what are some of the key benefits of using packages?
Enhance and maintain applications more easily
As more and more of the production PL/SQL code base moves into maintenance mode, the quality of PL/SQL applications will be measured as much by the ease of maintenance as they are by overall performance. Packages can make a substantial difference in this regard. From data encapsulation (hiding all calls to SQL statements behind a procedural interface to avoid repetition), to enumerating constants for literal or “magic” values, to grouping together logically related functionality, package-driven design and implementation lead to reduced points of failure in an application.
Improve overall application performance
By using packages, you can improve the performance of your code in a number of ways. Persistent package data can dramatically improve the response time of queries by caching static data, thereby avoiding repeated queries of the same information (warning: this technique is not to be relied upon in stateless applications). Oracle’s memory management also optimizes access to code defined in packages (see OPP6 Chapter 24 for more details).
In addition, when you invoke one subprogram in a package, the entire package is loaded into memory. Assuming you have designed your packages well (see "Keep your packages small and narrowly focused" below), this will
Shore up application or built-in weaknesses
It is quite straightforward to construct a package on top of existing functionality where there are drawbacks. (Consider, for example, the UTL_FILE and DBMS_OUTPUT built-in packages in which crucial functionality is badly or partially implemented.) You don’t have to accept these weaknesses; instead, you can build your own package on top of Oracle’s to correct as many of the problems as possible.
For example, the do.pkg script I described in OPP6 Chapter 17 offers a substitute for the DBMS_OUTPUT.PUT_LINE built-in that adds an overloading for the XMLType datatype. Sure, you can get some of the same effect with standalone procedures or functions, but overloading and other package features make this approach vastly preferable.
Minimize the need to recompile code
As you will read below, a package usually consists of two pieces of code: the specification and body. External programs (not defined in the package) can only call programs listed in the specification. If you change and recompile the package body, those external programs are not invalidated. Minimizing the need to recompile code is a critical factor in administering large bodies of application logic.
And I finish up with a few recommendations for writing PL/SQL code:
Avoid writing schema-level procedures and functions
Always start with a package. Even if there is just one program in the package at the moment, it is very likely that there will be more in the future. So "put in the dot at the start," and you won't have to add it later.
Use packages to group together related functionality
A package gives a name to a set of program elements: procedures, functions, user-defined types, variable and constant declarations, cursors, and so on. By creating a package for each distinct area of functionality, you create intuitive containers for that functionality. Programs will be easier to find, and therefore less likely to be reinvented in different places in your application.
Keep your packages small and narrowly focused
It doesn't do much good to have just three packages, each of which has hundreds of programs. It will still be hard to find anything inside that bunch of code. Instead, keep your packages small and focused: all subprograms in a package should be "related" in some fashion that is reflected by the package name, and that are commonly used together or in close proximity.
And if you do have a monster package that you need to break up into more manageable pieces, be sure to check out the new-to-12.1 ACCESSIBLE_BY feature.
Do not forget that using packages moves all SQL out of the application and into the data dictionary where it can be managed and tuned.
ReplyDeleteWith reference to the below article in Oracle magazine, you mentioned some of disadvantages of packages? Is that still true from 11g or 12?
ReplyDeletehttp://www.oracle.com/technetwork/issue-archive/2005/05-may/o35plsql-085074.html
Tom’s suggestion is … Always use packages for real code. The packages are loaded in memory in chunks
Thanks for your time and sharing your comments.
Tom and I are certainly on the same page. Yes, the package should be the "go to" unit for application construction on Oracle Database.
DeleteThe concerns I raise in that article are still valid. But unless you are pursuing object-oriented development with PL/SQL, you will almost never need or want to have schema-level procs and funcs.
In fact, in that article, I called out a use case for pulling a single function out a package to isolate impact of changes. I could just have easily have said: move that function into its own package.
Thanks Master for your response. You are the best and I can’t think of PL/SQL without you. Thanks for sharing the knowledge.
DeleteCould you please shed some more lights on the above lines from your response?
“Unless you are pursuing object-oriented development with PL/SQL, you will almost never need or want to have schema-level procs and funcs.”
That is a very kind comment (though I don't really like to be called "master") - in particular because that sentence MAKES NO SENSE. I meant to write:
DeleteBut unless you are pursuing object-oriented development with PL/SQL, you will almost never need or want to use anything but packages for your code base.
Is that more clear? :-)
Extremely sorry Steven Feuerstein. I did not mean to hurt your feelings. Just typed my mind's reflection. I am spending almost an hour to learn new things on daily basis in your blog. The way you narrate the concepts in PL/SQL is mind blowing. Thanks for your service to oracle community.
DeleteHurt my feelings? Not at all! I am glad you called out that sentence, so I could correct it. You are helping me do a better job, please continue and thanks for the kind words.
Delete