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.
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).
If you are a programmer - you would see the benefits of packages over a proliferation of standalone procedures in a heartbeat.
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.
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.
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
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.
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.
Karthik on Apr 29, 2020 at 11:04 am
This article is contributed by Karthik. If you like dEexams.com and would like to contribute, you can write your article here or mail your article to firstname.lastname@example.org . See your article appearing on the dEexams.com main page and help others to learn.