Systems Engineering and RDBMS

Archive for July 15th, 2007

Usage of a Package vs Procedures

Posted by decipherinfosys on July 15, 2007

At one of the client sites where they are using Oracle, the schema is littered with procedures and the chief DBA asked us last week about our opinion on whether they should be using procedures or packages. Our opinion on this is that one should always make use of packages. Here are the benefits:

  1. Encapsulation: Usage of packages allows for writing modular, easy to understand code.
  2. It supports overloading.
  3. It breaks the dependency chain i.e. no cascading invalidations whenever you install a new package body. In lieu of that, in case you have procedures that call other procedures, compiling one leads to invalidations.
  4. Related objects (procedures and functions) can be grouped together easily.
  5. Usage of package specification. Say you have 5 procedures and 2 are meant to be called by others and 3 are just internal routines that no-one should be privy to. The specification exposes the 2 but not the others. This way, you can control what can be used by others and what cannot. Here is an example:

SQL> create or replace package package_demo
2 as
3 procedure p1;
4 procedure p2;
5 end;
6 /

Package created.

SQL> create or replace package body package_demo
2 as
3 procedure p5 as begin null; end;
4 procedure p4 as begin null; end;
5 procedure p3 as begin null; end;
6 procedure p2 as begin null; end;
7 procedure p1 as begin null; end;
8 end;
9 /

Package body created.

SQL> desc package_demo

So, in real production code, one should strive to use packages. In demo environments or test environments or when you are playing with features, it would be fine to make use of procedures in lieu of the packages but real production code should be using packages.

Posted in Oracle | Leave a Comment »