x

Package and Package Body

Can anyone give a clear picture on Procedure, Package and Package Body in Oracle?

more ▼

asked Dec 16, 2009 at 08:17 PM in Default

Tariq Rahiman gravatar image

Tariq Rahiman
665 2 2 4

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Packages make the database a much better programming environment by introducing the concept of an interface, which allows encapsulation and abstraction. They allow you to effectively develop and maintain complex applications written in PL/SQL

In MS SQL Server and similar, which just have stored procedures, there is no concept of an external and internal API; all sprocs are at the same level, available to be called by any code. There's no way for a developer to present a limited interface to the outside worlds, all the internal methods are open. Without rigorous controls on development procedures, this can (and does) lead to "spaghetti code", where there's a complex and poorly understood interactions between various stored procedures. It makes ongoing maintenance much more complex as there's no limit to what side effects might occur when a stored procedure is changed.

Packages solve this problem by encapsulating related stored procedures into a single package, and allowing a limited external interface to be defined. This means that the majority of the stored procedures are only ever called within the package and are not exposed to the outside. The impact of changing these is limited to the package. (If you end up with a lot of public stored procedures, you might want to reconsider your interface design and application boundaries to reduce that).

Packages also allow you to limit the namespace, and the scope of variables and exceptions to within that package, meaning there are no side effects. You can develop packages independently of each other without worrying that two packages both have a sproc called "GetCustomer" or a global variable called updateFlag. The reduction in the number of places the code can possibly interact to just the public interfaces of each package greatly simplifies new development and maintenance of the system.

Essentially packages fulfill the role of Class Libraries in .NET or Java, and make the database a useful development environment for large scale software systems. Don't let the application developers tell you they need to do all the work in Java and database stored procedures are just for SQL. PL/SQL is a powerful enough language to usefully manage all the logic for a complex system. (There are pros and cons of taking that approach, but it's a valid direction to consider).

more ▼

answered Dec 17, 2009 at 11:52 AM

Andrew Mobbs gravatar image

Andrew Mobbs
1.5k 1 3

(comments are locked)
10|1200 characters needed characters left

Pacakge contains proto type like definiting procedure , function signatures and any variables declaration.

Package contains Package body where all procedures and functions actual definitions will be defined.

Procedure is nothing but a pl/sql block where all required processing code will be written to do a specific task. Its like fucntion but it won't return any value.

more ▼

answered Dec 16, 2009 at 08:36 PM

BI DWH BALA gravatar image

BI DWH BALA
606 43 60 62

In other rdbms like DB2 UDB, MySQL, MSSQL - there is no concept as package Body and Packages. So what are the advantages of Packages and Package Body. How do we take the decision when to use a Package and Package Body. I know I can write application logic in a procedure, just like any othe RDBMS.
Dec 16, 2009 at 08:45 PM Tariq Rahiman

Package helps us to group all related functionality of an application under one pack. The concept is same as OOPS concept. So In Pacakage, as I explained above contains all signature/prototypes which are going to be defined in Pacakge body.

Let's take an expample of oracle pre-defined pacakges such as DBMS_STATS. This is a package. This package contains all procedures, functions and so on belong to Stats collection functions. In package body we can see the actual defintions of procedures.

Calling a procedure in package is DBMS_STATS.MyProc()
Dec 16, 2009 at 09:29 PM BI DWH BALA
http://www.oracle.com/technology/oramag/oracle/05-may/o35plsql.html .. May be this would help for detailed explanation.
Dec 16, 2009 at 09:35 PM BI DWH BALA
Packages also allow you to hide implementation details. A Package Spec allows someone to run a program w/o access to the details.
Dec 16, 2009 at 10:56 PM HillbillyToad
(comments are locked)
10|1200 characters needed characters left

There's discussion of packages vs procedures on AskTom:

AskTom

My take on this is that procedures (and functions) are good for almost anything but implementing a system of procedures (and functions) should involve a package.

A package for definitions plus a package body can be used to hide, as another said, implementation details and also to avoid distracting the clients or users with the details that they don't really need to see, like work variables, internally called functions, etc. The one downside to that is that you might be developing something that they'd find useful otherwise but they can't access it (like a function to do a special kind of replace or translation could be generally useful elsewhere).

more ▼

answered Dec 17, 2009 at 11:54 AM

KillerDBA gravatar image

KillerDBA
1.5k 8 9 10

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x50
x27
x24
x13
x1

asked: Dec 16, 2009 at 08:17 PM

Seen: 3797 times

Last Updated: Dec 17, 2009 at 06:15 PM