An Oracle package is a stored sub-program structure that combines related procedures and functions into a single unit. Imagine it as a neatly organized toolbox containing various tools (procedures and functions) for specific tasks (related functionalities within your database).
Here's a breakdown of why Oracle packages are so useful:
Organization and Encapsulation:
- Packages group logically related procedures and functions, promoting code organization and clarity. Think of it like grouping screwdrivers, hammers, and nails in separate compartments within your toolbox instead of having them scattered around.
Reusability and Modularity:
- Procedures and functions within a package can be easily reused across different parts of your database code, reducing redundancy and improving maintainability. It's like having multiple projects needing screwdrivers, and instead of buying a new one for each, you simply reach for the one in your trusty toolbox.
Data Hiding and Access Control:
- Packages can control access to procedures and functions by declaring them as `PUBLIC` (accessible to all) or `PRIVATE` (accessible only within the package). This promotes data security and prevents unauthorized modifications. Like having a lock on your toolbox to keep curious hands away from sensitive tools.
Improved Performance:
- Storing procedures and functions within a package can enhance performance as they are pre-compiled and readily available for execution. It's like having your tools pre-sharpened and ready to use, saving you time and effort when you need them.
In summary, Oracle packages offer a powerful and versatile way to organize, reuse, and manage your database code, contributing to cleaner, more efficient, and secure database applications. I hope this explanation clarifies the concept of Oracle packages for you! Feel free to ask if you have any further questions about their specific functionalities or implementation.
Now that you have seen how to create procedures and functions, you are ready to work on a more advanced stored sub-program structure called the package. A package combines procedures and functions into a single unit. By the end of this module, you will be able to:
- Define a package and its components
- Describe appropriate uses of a package
- Build the outer shell of a package
- Build the package body
- Create a PL/SQL block that calls different parts of a package
The next lesson defines packages and appropriate uses of packages.
A
package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions. A package is compiled and stored in the database, where many applications can share its contents. You can think of a package as an application and you can write your own packages. You can also use the many product-specific packages that Oracle Database supplies.
Oracle supplies many PL/SQL packages with the Oracle server to extend
database functionality and provide PL/SQL access to SQL features. You can use the supplied packages when creating your applications or for ideas in creating your own stored procedures.
This module covers the packages provided with the Oracle database server. Packages supplied with other products, such as Oracle Developer or the Oracle Application Server, are not covered. Note that not every package or type described in this module is installed by default. In such cases, the documentation states this and explains how to install the object.
Run this query as a suitably privileged user:
SELECT DISTINCT Owner, Object_Type, Object_Name FROM DBA_Objects_AE
WHERE Owner IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'MDSYS', 'LBACSYS',
'DMSYS', 'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM')
AND Object_Type IN ('PACKAGE', 'TYPE')
ORDER BY Owner, Object_Type, Object_Name
This lists every Oracle-supplied package and type that is currently installed in the database.
Note that it lists a number of objects not mentioned in the Oracle Database Documentation Library.
This is deliberate. Some of the Oracle-supplied packages and types are intended to be used only by other Oracle-supplied components.
Any package or type that is not described in the Oracle Database Documentation Library is not supported for direct customer use.
