Try it here
Oracle Packages, Procedures and Functions

Advantages and disadvantages of Package


A package is a group of PL/SQL types, objects, and stored procedures and functions. The specification part of a package declares the public types, variables, constants, and subprograms that are visible outside the immediate scope of the package.

The body of a package defines the objects declared in the specification, as well as private objects that are not visible to applications outside the package.


Modular approach, Encapsulation/hiding of business logic, security, performance improvement, reusability.

Which is the best and when to use standalone procedures

As per astom

Always use a package.

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


  • break the dependency chain (no cascading invalidations when you install a new package body -- if you have procedures that call procedures -- compiling one will invalidate your database)
  • support encapsulation -- I will be allowed to write MODULAR, easy to understand code -- rather then MONOLITHIC, non-understandable procedures
  • increase my namespace measurably. package names have to be unique in a schema, but I can have many procedures across packages with the same name without colliding
  • support overloading
  • support session variables when you need them
  • promote overall good coding techniques, stuff that lets you write code that is modular, understandable, logically grouped together....

If you are a programmer - you would see the benefits of packages over a proliferation of standalone procedures in a heartbeat.

Description of some features:

break the dependency chain

Don't use standalone procedures! Use packages. Packages break the dependency chain.

If procedure A calls procedure B and B is "changed", then A is directly affected and must be recompiled. There is no "auto recompile A when B changes" option -- although A will recompile itself automatically the next time it is run.

On the other hand, If I create a package PKG_A with a procedure A and a package PKG_B with a procedure B then PKG_A will be dependent on PKG_B's specification. I can compile and recompile PKG_B's Body as often as I like without affecting PKG_A's state. As long as PKG_B's specification or interface does not change -- dependent objects are not affected by the recompilation of the body.

Security and maintainability through private code

With packages, you can specify which subprograms, types and items are public (visible and accessible outside the package) or private (hidden and inaccessible outside the package). For example, if a package contains four subprograms, three might be public and one private. The ability to have private portions in a package ensures that subprograms and other constructs that need not, or SHOULD not, be accessible publicly – remain hidden. This protects the integrity of the package, and also simplifies maintenance – any change to a private element impacts only this package and nothing else.

Session-wide persistence of public variables

Packaged public variables and cursors persist for the duration of a session. They let you maintain data across transactions without storing it in the database. All subprograms that run in the environment share public package variables and cursors, and will read/edit the same values. This unique feature of PL/SQL packages can be used by applications for storing values with session-wide relevance, such as trace/debug options

Better performance

Package can seriously improve performance in a database by reducing or removing the amount of times things get compiled. If you do everything in standalone procedures -- a simple fix to one procedure might make your entire set of routines go invalid (a calls b, c calls a, d calls c and so on. if you update B, a goes invalid -- when a recompiles c will -- then d and so on). Packages can make it so that none of this happen.

When you invoke a packaged subprogram for the first time, the entire package is loaded into memory. Later calls to subprograms in the same package need no disk I/O. This translates to better performance.


More memory may be required on the Oracle database server when using Oracle PL/SQL packages as the whole package is loaded into memory as soon as any object in the package is accessed.

Updating one of the functions/procedures will invalid other objects which use different function/procedures since whole package need to be compiled.

When to use Standalone Procedures/Functions:

Use it only for testing,experimenting,demoing. Going to production, using real code, in the real world -- it is all about packages and nothing but.

Writer profile pic

Karthik on Apr 29, 2020 at 11:04 am

This article is contributed by Karthik. If you like and would like to contribute, you can write your article here or mail your article to . See your article appearing on the main page and help others to learn.

Post Comment

Comments( 0)




Forgot Password

Please enter your email address below and we will send you information to change your password.