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.