Managing Program Code Stored in the Database

About Program Code Stored in the Database

Oracle Database offers the ability to store program code in the database. Developers write program code in PL/SQL or Java, and store the code in schema objects. You can use Oracle Enterprise Manager Database Control (Database Control) to manage program code objects such as:

• PL/SQL packages, procedures, functions, and triggers

• Java source code (Java sources) and compiled Java classes

The actions that you can perform include creating, compiling, creating synonyms for, granting privileges on, and showing dependencies for these code objects. You access administration pages for these objects by clicking links in the Programs section of the Schema subpage.

Note that creating and managing program code objects is primarily the responsibility of application developers. However, as a DBA you might have to assist in managing these objects. Your most frequent task with respect to program code objects might be to revalidate (compile) them, because they can become invalidated if the schema objects on which they depend change or are deleted.

Validating (Compiling) Invalid Schema Objects

As a database administrator (DBA), you may be asked to revalidate schema objects that have become invalid. Schema objects are invalidated when changes are made to objects on which they depend. For example, if a PL/SQL procedure contains a query on a table, if the table is deleted or changes are made to its structure, the PL/SQL procedure becomes invalid. You revalidate schema objects by compiling them.

Database Control notifies you when schema objects become invalid by displaying an alert in the Alerts section of the Database Home page.

There are two ways to display schema objects that require validation: by following an alert on the Database Home page, or by viewing the appropriate object page (Views page, Procedures page, and so on) and searching for the objects.

To validate schema objects starting from an alert:

1. Go to the Database Home page, logging in as user SYSTEM.

   See "Accessing the Database Home Page".

2. In the Alerts section, search for alerts with the following message:

   n object(s) are invalid in the schema_name schema.
   

   An example of such a message is the following:

   4 object(s) are invalid in the HR schema.
   

   There is a separate message for each schema that contains invalid objects.

   
   Description of the illustration invalid_object_alerts.gif
   
   

3. In the Message column, click an invalid object message.

   The Owner's Invalid Object Count page appears.

4. Under Related Links, click Invalid Objects Details.

   The Invalid Object Details page appears, showing a list of invalid objects.

   
   Description of the illustration invalid_object_details.gif
   
   

5. Select one or more objects, and then click Recompile.

   A confirmation message appears, and the newly validated objects are removed from the list.

To validate a schema object starting from an object page:

1. Go to the Database Home page, logging in as user SYSTEM.

   See "Accessing the Database Home Page".

2. At the top of the page, click Schema to view the Schema subpage.

3. In the Database Objects section, click the link for the object type that you want to validate.

   For example, if you want to validate a view, click Views.

4. On the object page (for example, the Views page), enter a schema name and, optionally, an object name or partial object name, and then click Go.

   The schema objects are displayed.

5. Select the schema object that you want to validate.

6. In the Actions list, select Compile, and then click Go.

   A confirmation message appears.