Managing Database-Resident Program Units

Oracle offers the capability to store programs in the database. This functionality enables commonly required code to be written and tested once and then accessed by any application that requires the code. Database-resident program units also ensure that the same processing is applied to the data when the code is invoked, making the development of applications easier and providing consistency between developers.

You can write database-resident programs in PL/SQL or Java. You can use Enterprise Manager to manage sou rce types such as PL/SQL packages, procedures, triggers, functions, and Java sources and classes. The actions include creating, compiling, creating synonyms for, granting privileges on, and showing dependencies for these source types. You can perform these actions by clicking the links in the Programs section of the Administration page, as shown in Figure 8-7.

Figure 8-7 Programs Section

Description of "Figure 8-7 Programs Section"

This book describes the main types of PL/SQL program units. The following sections cover:

Managing Packages
Managing Package Bodies
Managing Standalone Subprograms
Managing Triggers

Note that creating and managing program units is the primary responsibility of an application developer. However, as a DBA you might be called upon to assist in managing these objects, since they can become invalidated through schema changes.

Managing Packages

A package is a structure that contains definitions, blocks of PL/SQL code, or both in a single unit. The contents of the package are invoked by naming the package and the element within it by means of a dot notation. For example, to execute the procedure called STORE_SQE in the CTX_QUERY package owned by the CTXSYS user, you would invoke it with the statement CTXSYS.CTX_QUERY.STORE_SQE(arg1,arg2) where arg1 and arg2 are the values for the two procedure arguments. To hide the complexity of packaged element names, you might want to use synonyms, described later in "Working with Other Schema Objects".

Viewing Packages

To see which packages exist in your database, use the search options on the Packages page.

To view packages:

In the Programs sections of the Administration page, click Packages.

The Packages page appears.
In the Schema box, enter the name of a schema. For example, enter SYS. Alternatively, click the flashlight icon adjacent to the Schema box to search for a schema.
Leave Object Name blank to search for all packages in the specified schema. Alternatively, enter an object name or click the flashlight icon to search for one.
Click Go.

The packages in the specified schema are displayed.
In the results list, select a package and then click View or the link in the Package Name column to view a package definition. For example, click the link for DBMS_ALERT.

The View Package: package_name page appears. This page contains the package name, schema, status, methods, and source. The source contains the code for a package and may contain the following elements:
- Authentication information—Determines which security schema is used when the package contents are executed
- Procedure definitions—Specify the name and list of arguments for one or more procedures.
- Function definitions—Specify the name, list of arguments, and return data type for one or more functions.
- Variable definitions—Specify variables that are globally available to the user of the package.
- Other public elements—Define such structures as types, constants, and exceptions that are needed by package users.

Creating Packages

As with the other database objects, you can use Enterprise Manager to create packages. This section explains how to create a package definition.

To create a package definition:

In the Programs sections of the Administration page, click Packages.

The Packages page appears.
Click Create.

The Create Package page appears.
In the Name, Schema, and Source boxes, enter the desired data. For example, enter the following information:
- In Name, enter test_package.
- In Schema, enter hr.
- In Source, enter a package definition. For more information on package definitions, see Oracle Database PL/SQL User's Guide and Reference.
Click OK.

An Update Message confirms the creation of the package definition.

Editing Packages

As with the other database objects, you can use Enterprise Manager to edit packages. This example edits the test_package package that you created in the previous section.

To edit packages:

In the Programs sections of the Administration page, click Packages.

The Packages page appears.
Enter search criteria in the Schema and Object Name boxes and click Go. For example, search for packages in the hr schema.

The packages in the specified schema are displayed.
In the results list, select a package and then click Edit. For example, select TEST_PACKAGE.

The Edit Package: package_name page appears. You can perform the following actions:
- Modify the package definition by typing new contents in the Methods and Source box and clicking Apply.
- Compile the package by clicking Compile, an action that may be required if the package status has become invalid for some reason.
- Modify the package body (see next section) by clicking Modify Package Body.

Note:

You can also navigate to the Edit Package property page from the Edit Package Body property page, described in the next section, by clicking Edit Package.

Dropping Packages

Under exceptional circumstances, such as when advised by Oracle support or an application developer, you might be required to drop a package. As with the other database objects, you can use Enterprise Manager to do so. This example drops the test_package package.

To drop packages:

In the Programs sections of the Administration page, click Packages.

The Packages page appears.
Enter search criteria in the Schema and Object Name boxes and click Go. For example, search for packages in the hr schema.

The packages in the specified schema are displayed.
In the results list, select a package and then click Delete. For example, select TEST_PACKAGE.

A Confirmation page appears.
Click Yes to delete the package

The Update Message confirms the deletion.

Managing Package Bodies

Package bodies contain the PL/SQL code for the procedures and functions defined in the definitions of the packages to which they belong. The same navigation paths and options for adding (creating), editing (modifying), compiling, and deleting package bodies are available in Enterprise Manager as for packages, as described in the previous section. These paths enable direct navigation between the Edit Package Body and the Edit Package property pages as mentioned in the previous section.

In some cases, the developer of the package may have wrapped one or more of the individual procedures or functions. Wrapping converts the code into non-readable strings which makes it difficult for other developers to misuse the application or for competitors to see the algorithms. Wrapped code cannot be edited. The Source box in the Edit Package Body page will contain a message such as Source code could not be displayed, because it is wrapped and will not be available for modification.

Viewing Package Bodies

To find out which package bodies exist in your database, use the search options on the Package Bodies page.

To view package bodies:

In the Programs sections of the Administration page, click Package Bodies.

The Packages page appears.
In the Schema box, enter the name of a schema. For example, enter SYS.

Alternatively, click the flashlight icon adjacent to the Schema box to search for a schema.
Leave Object Name blank to search for all packages in the specified schema.

Alternatively, enter an object name or click the flashlight icon to search for one.
Click Go.

The packages in the specified schema are displayed.
In the results list, select a package and then click View or the link in the Package Body Name column to view a package body. For example, click the link for CONNECTIONINTERFACE.

The View Package Body: package_name page appears. This page contains the package name, schema, status, and source. The source contains the code for a package. Note that the Source box for the CONNECTIONINTERFACE package indicates that the package is wrapped.

Managing Standalone Subprograms

Procedures and functions that are created outside of a package are called standalone subprograms. To execute a standalone subprogram, you only need to include its schema name and object name; there is no package name. Even so, you may want to create synonyms for subprograms that are used regularly.

A PL/SQL procedure is a subprogram that performs a specific action. You specify the name of the procedure, its parameters, its local variables, and the BEGIN-END block that contains its code and handles any exceptions. A function is a subprogram that computes a value. Functions and procedures are structured alike, except that functions return a value.

You can use Enterprise Manager to create, modify, compile, and delete standalone subprograms just like packaged subprograms except that they do not have a parent package (and, consequently, no navigation path in Enterprise Manager from or to the package property pages). In the Programs section of the Administration page, click the links for Procedures and Functions to navigate to the relevant property pages.

Managing Triggers

A database trigger is a stored subprogram associated with a database table, view, or event. For instance, you can have Oracle fire a trigger automatically before or after an INSERT, UPDATE, or DELETE statement that affects a table.