| Lesson 3 | How are packages used? |
| Objective | Describe appropriate uses of packages. |
How are Packages used in Oracle?
Packages have several unique advantages and some interesting uses.
If your system has enough memory, packages are advantageous because all sub-components can be used without requiring additional read operations. The following table describes the advantages of using packages and some examples of their use.
| Advantage | Example |
| Logical sets of functions and procedures are grouped together in one package. | Packages are useful for complex data handlers, such as user-defined checklists. The package keeps all the tightly connected procedures and functions in a single package. With good documentation, these sub-programs are easier to maintain with changes and enhancements. |
| Package specifications are created without creating package bodies. | Packages are often used when you have several shared functions. You are designing a new accounting system in which several shared functions are needed to calculate totals and summary data. The exact tables and columns to be summarized are still not defined. You create the package specification that defines the names and parameters for each of the shared functions. Design of the applications that use these functions can continue, even though the completed functions are not done. When you complete the table design, then you create the package body. |
| Packages hide functionality. | Because you can define private functions and procedures, packages can hide details about your database schema. A good use for this is a security validation package that queries sensitive tables. The public portion of the package need not contain passwords or other sensitive data – it can all be inside the package. |
The disadvantages of using packages are:
- Packages are more complex to build than creating stand-alone procedures and functions.
- Packages are loaded into memory all at once, even if you only use one component within the package. This is a disadvantage if your system is strained for memory space. In this case, you may be better off separating the package into its sub-components.
Using Oracle Supplied Packages
Most Oracle supplied packages are automatically installed when the database is created. Certain packages are not installed automatically.
Special installation instructions for these packages are documented in following lessons.
To call a PL/SQL function from SQL, you must either own the function or have EXECUTE privileges on the function. To select from a view defined with a PL/SQL function, you must have SELECT privileges on the view. No separate EXECUTE privileges are needed to select from the view. Instructions on special requirements for packages are documented on the succeeding pages. The next lesson shows you how to begin coding a package.
Package Concepts - Quiz