23.6.1 Displaying Object Dependencies

The RDBMS keeps track of dependencies between stored objects so that it can make sure the compiled source code of an object is still valid. Whenever you create or replace a module, the PL/SQL engine compiles that program and stores both source and compiled code. When you execute the program, the compiled code is loaded into the shared pool and run. If procedure A calls procedure B, then whenever procedure B is modified, the compiled code for procedure A is no longer valid (this status is maintained in the USER_OBJECTS view, or at least in the underlying SYS.OBJ$ table). Use the USER_DEPENDENCIES view to see which objects reference or depend on a particular object, as shown in this example:

/* Filename on companion disk: userdpnd.sql */
SELECT referenced_name,
       referenced_type,
       referenced_owner,
       referenced_link_name
  FROM user_dependencies
 WHERE name = UPPER ('&1');

where &1 is a single parameter to the SELECT statement. If this statement were placed in a file named dependon.sql, then you could find all objects that reference the calc_totals procedure by entering the following command at the SQL> prompt:

SQL> start dependon calc_totals

23.6.2 Displaying Information About Stored Objects

The USER_OBJECTS view contains the following key information about an object:

OBJECT_NAME

Name of the object

OBJECT_TYPE

Type of the object

STATUS

Status of the object: VALID or INVALID

Here are the types of objects that are accessible through this view:

SQL> select distinct object_type from user_objects;

OBJECT_TYPE
-------------
FUNCTION
INDEX
PACKAGE
PACKAGE BODY
PROCEDURE
SEQUENCE
SYNONYM
TABLE
TRIGGER

9 rows selected.

You can see that USER_OBJECTS does more than keep track of PL/SQL code. You can use USER_OBJECTS to obtain a list of all PL/SQL objects currently in the database. I created and ran the following SQL*Plus script in a file called psobj.sql:

/* Filename on companion disk: psobj.sql */
SET PAGESIZE 66
COLUMN object_type FORMAT A20
COLUMN object_name FORMAT A30
COLUMN status FORMAT A10
BREAK ON object_type SKIP 1
SPOOL psobj.lis
SELECT object_type, object_name, status
  FROM user_objects
 WHERE object_type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
 ORDER BY object_type, status, object_name
/
SPOOL OFF

The output file from this script file contained the following list:

OBJECT_TYPE          OBJECT_NAME                    STATUS
-------------------- ------------------------------ ----------
FUNCTION             DEVELOP_ANALYSIS               INVALID
                     NUMBER_OF_ATOMICS              INVALID
                     FREQ_INSTR                     VALID

PACKAGE              CHECKS                         VALID
                     CONFIG_PKG                     VALID
                     DBG                            VALID
                     DO                             VALID
                     EXCHDLR_PKG                    VALID

PACKAGE BODY         DBG                            VALID
                     DO                             VALID
                     EXCHDLR_PKG                    VALID

PROCEDURE            ASSESS_POPULARITY              INVALID
                     ASSERT_CONDITION               VALID

Notice that a number of my modules are INVALID. This may be due to changes to the tables referenced in the modules, or by changes to other programs called by these modules. The RDBMS automatically recompiles these objects when a program tries to call them. In other words, this recompilation takes place at run time, when the user has caused these programs to be run, waiting while the compilation occurs. You can avoid this automatic recompilation (and impact on users) by manually compiling the INVALID modules yourself. The best way to do this is to generate the compile command for each invalid module, directly from the USER_OBJECTS table.

When I use SQL to generate a list of commands to be executed, I save the SQL statement into a file with a .sql extention. The output from this file is sent to another file, this time with a .cmd extention (since it contains CoMmanDs). I then execute that command file at the end of the SQL script.

The code below generates the SQL commands needed to force recompilation of any invalid PL/SQL objects. In addition to creating the ALERT...COMPILE command, it adds a SHOW ERRORS command after each compile attempt so that I can see any errors from an unsuccessful compile. I set the line size and lengths of the output strings so that each command goes on its own line in the command file. Finally, when I run the recomp.cmd file (generated by the SELECT statement), I spool the output to another file (recomp.lis) so that I can review the results.

/* Filename on companion disk: recomp.sql */
SET PAGESIZE 0
SET LINESIZE 80
COLUMN command_line1 FORMAT A75
COLUMN command_line2 FORMAT A75
SPOOL recomp.cmd
SELECT 'ALTER '||
       DECODE (object_type, 'PACKAGE BODY', 'PACKAGE', object_type) || ' '||
       object_name || ' ' ||
       DECODE (object_type,
          'PACKAGE', 'COMPILE SPECIFICATION;',
          'PACKAGE BODY', 'COMPILE BODY;' ,
          'COMPILE;') command_line1,
       'SHOW ERRORS' command_line2
  FROM user_objects
 WHERE object_type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
   AND status = 'INVALID'
 ORDER BY
    DECODE (object_type,
       'PACKAGE', 1, 'PACKAGE BODY', 4, 'FUNCTION', 2, 'PROCEDURE', 3)
/
SPOOL OFF
SPOOL recomp.lis
START recomp.cmd
SPOOL OFF

23.6.3 Analyzing the Size of PL/SQL Code

In the Windows environment, you run into severe memory problems when your program size (for a standalone procedure or function, or an entire package) approaches and exceeds 32K. Now, thirty-two thousand-odd bytes is a very large size for a program. If you are writing a program this large, you should probably break it up into smaller pieces. You can use the USER_OBJECT_SIZE view to tell you about the size of your code, and use it as an early warning system for further code modularization.

This view also comes in handy when you want to balance the size of your programs and packages. The compiled code for a stored object must be present in the Shared Global Area of the database before it can be run. A certain amount of space is set aside in the SGA for program code. Suppose you have one program or package that is much larger than anything else and takes up much of the room in the shared pool. Whenever that program is executed or a reference is made to any of the package's objects, the RDBMS has to flush the other programs out of shared memory to make room for this single, massive chunk of code. Those other programs then have to be read back into the shared pool when next invoked. If you balance your code size, then you can minimize the disk I/O required to make stored objects available for execution.

The USER_OBJECT_SIZE view contains information about the size of the source code, the size of the parsed code, and the size of the compiled code. The following code shows the various sizes for each of your larger stored objects:

/* Filename on companion disk: pssize.sql */
SET PAGESIZE 66
COLUMN name FORMAT A30
COLUMN type FORMAT A15
COLUMN source_size FORMAT 999999
COLUMN parsed_size FORMAT 999999
COLUMN code_size FORMAT 999999
TTITLE 'Size of PL/SQL Objects > 2000 Bytes'
SPOOL pssize.lis
SELECT name, type, source_size, parsed_size, code_size
  FROM user_object_size
 WHERE code_size > 2000
 ORDER BY code_size DESC
/
SPOOL OFF

By only looking at PL/SQL objects with more than 2000 bytes, I can focus on the larger objects that might conceivably require attention. The output from pssize.sql is shown below:

Sun Dec 11                                                    page    1
                  Size of PL/SQL Objects > 2000 Bytes

NAME                  TYPE            SOURCE_SIZE PARSED_SIZE CODE_SIZE
--------------------- --------------- ----------- ----------- ---------
PS_GLOBAL             PACKAGE BODY          23434       29199     22584
DBG                   PACKAGE BODY          17011       23691     13793
PARSER_PKG            PACKAGE BODY          12367       11441      9795
PS_LIST               PACKAGE BODY           9893       11728      9488
COMPILER_PKG          PACKAGE BODY           5500        6262      4583
COMPILER              PACKAGE BODY           2374        4613      3650
NUMBER_OF_ATOMICS     FUNCTION               2106        4033      3056

I do have a wide disparity in the sizes of the packages and modules currently stored in the database. If I were running a production environment with this range of code size, I should look at balancing the code by breaking up the larger packages, particularly ps_global. In reality, however, these packages stand out mostly because my development database is devoid of the large application-specific modules and packages any normal production environment would include.

Regardless of whether you need to balance your code, you should never just build "one big package" to hold all of your programs. Break up your modules into logical sets of operations and data structures, and create packages for each of those.

23.6.4 Displaying and Searching Source Code

You should always maintain the source code of your programs in text files (or a development tool specifically designed to store and manage PL/SQL code outside of the database). When you have stored these programs in the database, however, you can take advantage of SQL to analyze your source code across all modules, which may not be a straightforward task with your text editor.

The USER_SOURCE view contains all of the source code for objects owned by the current user. The structure of USER_SOURCE follows:

 Name                            Null?    Type
 ------------------------------- -------- ----
 NAME                            NOT NULL VARCHAR2(30)
 TYPE                                     VARCHAR2(12)
 LINE                            NOT NULL NUMBER
 TEXT                                     VARCHAR2(2000)

where NAME is the name of the object, TYPE is the type of object (PROCEDURE, FUNCTION, PACKAGE, and PACKAGE BODY), LINE is the line number, and TEXT is the text of the source code.

So you could look for all programs containing a certain substring in its source code. You could return the text associated with a specific line number. You could get a list of all the packages defined in your schema, and so on. The following sections offer some concrete examples.

23.6.5 Cross-Referencing Source Code

Using SQL, for example, you can discover the set of programs that reference a particular global variable or even PL/SQL built-in. The SQL statement below uses a single parameter so that you can specify the string for which you want to search:

SELECT DISTINCT name
  FROM user_source
 WHERE INSTR (UPPER (text), '&1') > 0;

You can view the source code of a stored object with the SQL statement shown below. The column line is the sequence number of that line of text in the program:

SELECT text
  FROM user_source
 WHERE name = UPPER ('&1')
 ORDER BY line;

If the query in this code were stored in pslist.sql, then I could obtain the source code for the function called number_of_atomics by entering the following command at the SQL> prompt:

SQL> start pslist number_of_atomics

NOTE: The underlying SOURCE$ table (upon which the USER_SOURCE view is built) does not retain the blank lines of your original source file.

23.6.6 Finding the Code for a Line Number

As noted earlier in the chapter, the output from a call to SHOW ERRORS in SQL*Plus displays the line number in which an error occurred, but that line number doesn't correspond to the line in your text file. Instead, it relates directly to the line number stored with the source code in the USER_SOURCE view.

The source_at_line function that follows provides an easy mechanism for retrieving the text from a stored program for a specified line number. It takes three parameters:

name_in

The name of the stored object

line_in

The line number of the line you wish to retrieve (default value is 1)

type_in

The type of object you want to view (default for TYPE is NULL)

The default values are designed to make this function as easy as possible to use. The following examples show the different ways source_at_line can be called:

• Retrieve the tenth line of code in the company package body:

  the_text := source_at_line ('company', 10, 'package body');

• Retrieve the fifth line of code from the calc_totals procedure. Notice that I do not specify the type of object. Since I know that there is only a calc_totals procedure and no other type of object with that name, I can safely let the cursor scan through all of USER_SOURCE:

  the_text := source_at_line ('calc_totals', 5);

• Retrieve the first line of the company_name function. I only supply the name of the function, so I use the default of the line = 1 and NULL type. This form of source_at_line would be handy for displaying the header of a module or package, which usually shows up on the first line.

  DBMS_OUTPUT.PUT_LINE (source_at_line ('company_name'));
  ==>
     FUNCTION company_name (company_id IN INTEGER) RETURN VARCHAR2

Here, then, is the code for the source_at_line function:

/* Filename on companion disk: srcline.sf */
FUNCTION source_at_line
   (name_in IN VARCHAR2,
    line_in IN INTEGER := 1,
    type_in IN VARCHAR2 := NULL)
RETURN VARCHAR2
IS
   CURSOR source_cur IS
      SELECT text
        FROM user_source
       WHERE name = UPPER (name_in)
         AND (type = UPPER (type_in) OR type_in IS NULL)
         AND line = line_in;

   source_rec source_cur%ROWTYPE;

BEGIN
   /* Open and fetch the line of code. */
   OPEN source_cur;
   FETCH source_cur INTO source_rec;

   IF source_cur%NOTFOUND
   THEN
       CLOSE source_cur;
       RETURN NULL;
   ELSE
       CLOSE source_cur;
       RETURN source_rec.text;
   END IF;
END;


   

23.6.7 Changing Source Code in the Database

If you want to be truly adventurous you could also update your source code in the data dictionary. The only reason to do this would be to perform application-wide changes to source code in files which were not possible or practical through the editor. You might, for example, want to perform name replacements: every program that called number_of_atomics should now call parser.numatoms because I moved the standalone module into a package.

You cannot update source code through the USER_SOURCE view. Instead, you must make changes directly to the SOURCE$ table owned by SYS. The columns for SOURCE$ are:

OBJ$

The object ID

LINE

The line number

SOURCE

The line of source code

You can obtain the object ID for a particular module from the USER_OBJECTS view, which contains the OBJECT_ID column. The code below offers a PL/SQL procedure that accepts an owner name, a program name, and the old and new versions of a string. The procedure then replaces all instances in that program of the old string with the new.

/* Filename on companion disk: srcupd.sp */
PROCEDURE src_update
   (owner_in IN VARCHAR2, program_in IN VARCHAR2,
    old_str_in IN VARCHAR2, new_str_in IN VARCHAR2)
IS
   /* Cursor uses all_objects since this is run from SYS */
   CURSOR obj_cur IS
      SELECT object_id
        FROM all_objects
       WHERE owner = owner_in
         AND object_name = program_in;
   obj_rec obj_cur%ROWTYPE;
BEGIN
   OPEN obj_cur;
   FETCH obj_cur INTO obj_rec;
   IF obj_cur%FOUND
   THEN
      UPDATE SOURCE$
         SET source = REPLACE (source, old_str_in, new_str_in)
       WHERE obj# = obj_rec.object_id;
   END IF;
   CLOSE obj_cur;
END;

If you ever do make changes like this, be sure to recompile all modified programs.