SQL & PL/SQL

Read the following from oracle documentation

>
Better Performance
When you call a packaged subprogram for the first time, the whole package is loaded into memory. So, later calls to related subprograms in the package require no disk I/O. Also, packages stop cascading dependencies and thereby avoid unnecessary recompiling. For example, if you change the implementation of a packaged function, Oracle need not recompile the calling subprograms because they do not depend on the package body.
>

Read about other advantages.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/packages.htm#i2408

Adding to the above,

Packages can take the advantage of namespace, as you can declare several procedures with same name in different packages.

G.

My own personal preference is packages, as they are more flexible - the function or procedure that is "private" to this process today may be reusable tomorrow - then all you have to do is add it to the package specification, and it is reusable. It is also easier to read if another developer needs to read or maintain your code later - more modular and easier to understand (IMHO). The better performance is a nice benefit, but I would choose packages either way.

Hi

Packages & stored procedures are database objects.

Packages are having so many features like..!!

Modularity
*************

Packages let you encapsulate logically related types, items, and subprograms in a named PL/SQL module. 

Each package is easy to understand, and the interfaces between packages are simple, clear, and well 

defined. This aids application development.

Easier Application Design
**************************

When designing an application, all you need initially is the interface information in the package 

specs. You can code and compile a spec without its body. Then, stored subprograms that reference the 

package can be compiled as well. You need not define the package bodies fully until you are ready to 

complete the application.

Information Hiding
*******************

With packages, you can specify which types, items, and subprograms are public (visible and accessible) 

or private (hidden and inaccessible). For example, if a package contains four subprograms, three might 

be public and one private. The package hides the implementation of the private subprogram so that only 

the package (not your application) is affected if the implementation changes. This simplifies 

maintenance and enhancement. Also, by hiding implementation details from users, you protect the 

integrity of the package.

Added Functionality
********************

Packaged public variables and cursors persist for the duration of a session. They can be shared by all 

subprograms that execute in the environment. They let you maintain data across transactions without 

storing it in the database.

Better Performance
*******************

When you invoke a packaged subprogram for the first time, the whole package is loaded into memory. 

Later calls to related subprograms in the package require no disk I/O.

Packages stop cascading dependencies and avoid unnecessary recompiling. For example, if you change the 

body of a packaged function, the database does not recompile other subprograms that invoke the 

function; these subprograms only depend on the parameters and return value that are declared in the 

spec, so they are only recompiled if the spec changes.

Function Overloading
*****************
you can use same procedure name more than one procedure with diff parameters and their datatypes.

Regards
KPR

* if you are satisfied with this answer make it as correct
* if you are ok withi this answer make it as helpful

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7452431376537

>
Always use a package.

Never use a standalone procedure except for demos, tests and standalone utilities (that
call nothing and are called by nothing)
>

The link provides the reasoning for this recommendation.

As others point out, the documentation does claim that packages improve performance. That's not a particularly accurate/ reasonable statement however.

The fact that the entire package is read into memory can be either a positive or a negative. If your session goes on to use many methods in the package, you save overall from reading everything in at once. If, on the other hand, you call a small utility function in a package and never call another method from that package, you pay the performance penalty of reading the entire package into memory rather than reading in the one small utility function you actually needed.

Even granting that there may be performance differences positive or negative, however, it is exceedingly unlikely that you would be able to measure the difference let alone that you would care. Unless you have the smallest of toy databases, you'll be doing many orders of magnitude more physical I/O reading in data than you will reading in code-- even if every piece of code was automatically loaded with no I/O, as a practical matter, you'd never notice the difference.

The bit about dependency tracking is meaningful but I have a hard time classifying it as "performance" since it really only applies to performance during a build. You're going to ensure that everything is compiled at the end of the build anyway so whether it takes a minute or two to walk the dependency stack compiling everything is relatively immaterial from a general "performance" standpoint. There is an availability benefit since builds may be able to happen more quickly with packages. But it's not what you would normally call "performance".

Justin

I agree with Justin.

Any specific task performed by a package is unlikely to perform better than the same task performed by a stored procedure.

But you should still use packages, for many other reasons.

Performance is not the only criteria to be considered when selecting beneficial features.

If it were designers would never add brakes to cars.