Skip to main content

Why You Should ALWAYS Use Packages

Received this request via email today:
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.

Labels:

Comments

  1. AnonymousMay 14, 2016 at 6:23 AM

    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.

    ReplyDelete
  2. AnonymousOctober 22, 2017 at 6:24 PM

    With reference to the below article in Oracle magazine, you mentioned some of disadvantages of packages? Is that still true from 11g or 12?
    http://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.

    ReplyDelete
    Replies
    1. Steven FeuersteinOctober 23, 2017 at 4:03 AM

      Tom and I are certainly on the same page. Yes, the package should be the "go to" unit for application construction on Oracle Database.

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

      Delete
    2. AnonymousOctober 23, 2017 at 6:30 PM

      Thanks Master for your response. You are the best and I can’t think of PL/SQL without you. Thanks for sharing the knowledge.
      Could 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.”

      Delete
    3. Steven FeuersteinOctober 24, 2017 at 3:14 AM

      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:

      But 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? :-)

      Delete
    4. AnonymousOctober 24, 2017 at 5:17 PM

      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.

      Delete
    5. Steven FeuersteinOctober 24, 2017 at 5:33 PM

      Hurt 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

Post a Comment

Popular posts from this blog

Running out of PGA memory with MULTISET ops? Watch out for DISTINCT!

A PL/SQL team inside Oracle made excellent use of nested tables and MULTISET operators in SQL, blending data in tables with procedurally-generated datasets (nested tables).  All was going well when they hit the dreaded: ORA-04030: out of process memory when trying to allocate 2032 bytes  They asked for my help.  The error occurred on this SELECT: SELECT  *    FROM header_tab trx    WHERE (generated_ntab1 SUBMULTISET OF trx.column_ntab)       AND ((trx.column_ntab MULTISET             EXCEPT DISTINCT generated_ntab2) IS EMPTY) The problem is clearly related to the use of those nested tables. Now, there was clearly sufficient PGA for the nested tables themselves. So the problem was in executing the MULTISET-related functionality. We talked for a bit about dropping the use of nested tables and instead doing everything in SQL, to avoid the PGA error. That would, however require lots of work, revamping algorithms, ensuring correctness, you know the score. Then my eyes snagge
4 comments
Read more

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch,
4 comments
Read more

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p
24 comments
Read more