Monday, October 31, 2005

Oracle Packages

What is a "package"?

According to the PL/SQL User's Guide and Reference, "A package is a schema object that groups logically related PL/SQL types, items and subprograms."

But I believe a package is far more than just a way of logically grouping objects together.

Before I digress, let's very briefly understand what a package is. It's probably easiest if you take a look at Oracle's documentation, which has a good description of packages and some examples:

PL/SQL User's Guide and Reference, Chapter 9: PL/SQL Packages:

Put simply, packages have two parts, the specification and the body.

The specification is the interface into the package. It describes all the types, variables, procedures (etc) within the package. Ideally the specification does not change.

The body has all the implementation. Inside the body you would actually write the procedures, assign the values, and have all the details and logic behind the package. Often (unless you are the developer), you can treat the body as a "black box."

As always, I'll refer you to Dan Morgan to understand the syntax of Oracle PL/SQL Packages:

Back to the story.

According to one of my favourite PL/SQL Authors Connor McDonald, "The main reason packages are not more widely adopted is that users are unaware of the benefits they offer."

Beyond the known, obvious advantage of grouping together related items, which is great for organising your code and "modularity", what are these benefits of which we are speaking?

1. Objects don't get invalidated when you makes changes to the body. That saves a lot of recompilation and makes changing the implementation much more painless. You will still have to recompile if you change the specification, but that's not something you should be doing very often.

2. You can "overload" subprograms (procedures/functions). You can have several subprograms with the same name, but with a different number of parameters, or different types. That is another thing that makes implementation changes more painless because you can keep legacy code if you like. You can also see the extra flexibility that offers developers.

3. You can have persistent variables throughout a session without storing anything in a database table. Packages can have variables and constants that are initialised when the packages is first used within a session, and then they are available for the remainder of the session for all future references to anything within that package. That comes in very handy.

4. Speaking of initialisation, being able to call a procedure automatically the first time a package is used within a session can also come in very handy.

5. You can take advantage of "encapsulation." In essence, you can hide the implementation details from users but still give them all the information they need to use the package. Since they aren't aware of the details, that means you can change them with minimal impact or risk. Packages also support private subprograms and variables which are available only to other subprograms within the package, and remain completely hidden and inaccessible to anything outside the package.

6. You may notice some performance improvement when using packages. When you first use a package, the entire package may be loaded into memory, meaning fewer disk I/Os as you use the related items within.

I'm sure there are other advantages, but those are the ones I've noticed.

Let me close with links to my favourite two articles about Packages, from two of my favourite PL/SQL experts:
Connor McDonald's Chapter 2 from Mastering Oracle PL/SQL: Practical Solutions

Steven Feuerstein's article "Picking Your Packages" from May/June 2005 Oracle Magazine

By the way, to use packages, just prefix the package name using dot notation

Check out PL/SQL Supplied Packages and Types Reference

It includes UTL_HTTP and DBMS_OUTPUT

But also check out UTL_FILE, DBMS_PIPE, DBMS_ALERT and the DBMS_XML stuff.

Hi...I have a question..Say if i create a custom package with package name as dbms_output and still i wanted to call the Oracle's built in dbms_output? Is there a way out?

Mail me at
no buddy u can't create it if u know do let me know at
Dear Vollman,

I am a developer at initial.I want aa deep knowledge in oracle features and impotance of packages. Please send me some usefull tutorial regarding pl/sql. I will be gratefull to u.
Hi All
i m a developer in oracle pl/sql programming ,
i wanna add up performance tuning technique in my knowledge base so kindly give me tips or trick or some practical solution about it.

Ramdhan Sharma

I am a developer at initial.I wanted to have good grip in oraclePL/SQL.
Please direct me.
Mail Me :
This comment has been removed by a blog administrator.
Hi Mr Robert,

I really like the way you have organised the features of Package. I was aware of few of them but when i followed one of your favorite link of Connor McDonald, i really felt that i was having very little knowledge of features of Package.

I am really thankful to you for your effort in putting this wonderful thing here.

Moreover i want to enhance my performance tuning skill so if you have some knowledge to share or have some useful pdfs then please do send me on my email id :

Thanks in Advance,
Vaibhav Patil
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?