Creating Packages with PL/SQL

Writing your first PL/SQL Package

by William Ryan

Although I don't get to do as much PL/SQL programming as I'd like anymore, Oracle and PL/SQL are my first loves in programming (although with a little luck, I may get to do a lot more of it soon). I was lucky enough to get to work with an Oracle 7.0 database at the beginning  of my career and then moved on to an 8i database.  A short time afterward, I went to work in an environment where I used SQL Server exclusively.  I know there are tons of partisan discussions on which is the 'best' database, but the fact of the matter is that there's a lot to love about both SQL Server and Oracle.  Truth be told, propertly implementation of E.F. Codd's brilliant work matter a whole lot more than the RDBMS that you store data in.  After working with both systems extensively, I've noticed a lot more that's similar about the two than the differences.  One feature of Oracle that really stands out is the concept of Packages.

In a nutshell, a package is roughly analogous to a Stored Procedure, but a very powerful one. They provide many advantages, but the main ones I can think of are:

1)  They allow you to group similar pieces of logic in one place.
2)  They allow you to store global pieces of data that can be accessed from many different places within your program.
3)  They allow you to expose certain variables, functions, cursors etc, while hiding pieces that you want hidden.
4)  They can be reused.
5)  They can allow you to change the way a program behaves without having to recompile it.
6)  They tend to offer much better performance over standard SQL Statements
7)  They mimic the logic of Object Oriented Programming.

To start out with, a package is comprised to two pieces:  a specification and a body.  If you are familiar with C++ programming, it's roughly analogous to seperating your header file from your class implementation.

A typical header declaration looks something like this:

CREATE OR REPLACE PACKAGE knowdotnet_RSS AS      c_articleID NUMBER (2,1) := 1.1;      CURSOR rss_Articles IS    SELECT Author_ID        , Article_Title        , Article_Teaser        , Article_Body    FROM tbl_Articles     WHERE ArticleID = c_arcticleID; FUNCTION ShowArticleInformation    RETURN SomeInformation END knowdotnet_RSS; /

Know that we have the description in place, we need  to  implement the body.  The main difference, other than the implementation details, is the inclusion of the word "BODY".

CREATE OR REPLACE PACKAGE BODY knowdotnet_RSS AS FUNCTION ShowArticleInformation    IS     v_SomeField VARCHAR2 BEGIN   SELECT SomeValue    FROM SomeTable     WHERE SomeField = v_SomeField        RETURN v_SomeField; END knowdotnet; /

That's pretty much all there is to creating a package.  One word of caution though...  You can create a Package body without a specification but you won't be able to use it.  Provided it doesn't contain errors, the Package will be stored in your database but it's Status will be flagged as Invalid.  This makes sense because it's not ready for prime time yet.  However, in the future when you create the spec, you can simply use ALTER PACKAGE  with the COMPILE option and the it will be compiled as VALID.