10.12 How do I…Rebuild statements to create stored modules?

Problem

I need a technique to rebuild the CREATE statements that originally built the stored modules in my database. I also need to generate DDL statements to recompile all (or invalid) stored modules in the database. How do I generate DDL scripts to accomplish this?

Technique

The USER_SOURCE data dictionary view contains the source code for all the modules in a user account’s schema. Each record in the view represents a single line of source code in a stored module. The LINE column contains the line number within the module, and the TEXT column contains the line of code. To list the source code of stored modules, the TEXT column should be queried from the view ordered by the TYPE and LINE columns. The TYPE column must be included, as package specifications and package bodies have the same name. The CREATE OR REPLACE keywords are prefixed to the first line of the source code using the DECODE function to generate rebuild statements.

Refer to How-To 10.6 to identify the need to manually recompile invalidly stored modules to avoid runtime compilation. The name and type of stored modules can be queried from the USER_OBJECTS data dictionary view. The ALTER keyword is prefixed with the COMPILE keyword as a suffix, to generate commands to force recompilation of stored modules. To recompile only invalid objects, a WHERE clause can be used in the query to return objects with an invalid status as WHERE STATUS = ‘INVALID’.

By default, both the package specification and the package body are compiled with the ALTER…PACKAGE…COMPILE command. Optionally, the PACKAGE keyword to compile the package specification only, or the BODY keyword to compile the package body only, can be specified as the keyword following the COMPILE keyword.

Steps

1. Run CHP10_39.SQL in SQL*Plus. The query contained in the file generates a CREATE OR REPLACE statement for all stored modules in the current user account’s schema. The HEADING SQL*Plus variable is set to OFF to suppress the heading output and run the query. Figure 10.39 shows the query used.

The DECODE function prefixes the first line of source code for each stored module with the CREATE OR REPLACE keywords. The first line of code is identified by a value of 1 in the LINE column. The ORDER BY clause ensures that the output of the source code for each module is in the correct order and that each module is separated. Including the TYPE column in the ORDER BY clause ensures that the order is correct for stored packages (a package specification and body have the same object name).

To save the DDL statements created by the query, include a SPOOL command to write the output to a file.

2. If you don’t want to rebuild the stored modules but simply recompile them, run the CHP10_40.SQL file in SQL*Plus. The query contained in the file generates an ALTER COMPILE statement for each stored module in the user’s schema and saves it in a script file. The HEADING, FEEDBACK, and TERMOUT SQL*Plus variables are set to OFF to suppress the column headings, row count, and output of query to screen. The script file is then invoked to compile the stored modules. Figure 10.40 shows the contents of the CHP10_40.SQL file.

The ALTER keyword is prefixed, and the COMPILE keyword is suffixed for each object queried from the USER_OBJECTS view. The output is a list of DDL statements that are saved to a file. The WHERE clause ensures that the objects returned by the query are stored modules in the user account’s schema. Note that PACKAGE BODY objects are excluded; both the package specification and body are compiled by default with an ALTER…PACKAGE…COMPILE statement. When the commands are spooled to the command file, the file is run using the START command to recompile the stored modules.

The ALTER and COMPILE keywords in the query are used to create valid DDL statements. To recompile invalid stored modules, include a WHERE clause in the preceding query, to return only those modules that have an INVALID status in the USER_OBJECTS view. The query adds a SHOW ERRORS command after each compile statement to display any errors during compilation of a stored module.

How It Works

Step 1 presents a query using the DECODE function to concatenate the CREATE OR REPLACE keywords to the beginning of the first line. The query generates DDL statements for rebuilding stored modules. In Step 2, a query on the USER_OBJECTS view is used to generate DDL statements to recompile stored modules.

Comments

There might be times when you want to rebuild a stored module from the source code or recompile invalid modules in the database. Use the USER_SOURCE and USER_OBJECTS data dictionary views to create the necessary DDL statements. Refer to the Comments section of How-To 10.3 to identify system privileges that are required to execute the DDL statements.