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.


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

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

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

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

    3. 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? :-)

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

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


Post a Comment

Popular posts from this blog

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

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel