Lesson 1

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:

1. Define a package and its components
2. Describe appropriate uses of a package
3. Build the outer shell of a package
4. Build the package body
5. 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.