[Chapter 23] 23.6 Using SQL to Examine Stored ObjectsChapter 23Managing Code in the Database 23.6 Using SQL to Examine Stored ObjectsSince the stored objects are contained in tables in the data dictionary, you can use SQL itself to get information about the currently available programs. The following views are the most useful to familiarize yourself with: USER_DEPENDENCIESThe dependencies to and from objects you ownUSER_ERRORSThe current set of errors for all stored objects you own. This view is accessed by the SHOW ERRORS SQL*Plus command, described earlier in this chapter. USER_OBJECTSThe objects you ownUSER_OBJECT_SIZEThe size of the objects you ownUSER_SOURCEThe text source code for all objects you ownUSER_TRIGGERSThe database triggers you ownYou can view the structures of each of these tables either with a DESC command in SQL*Plus or by referring to Appendix B in Oracle Corporations's Oracle7 Server Administrator's Guide. The following sections provide some examples of the ways you can use these tables. 23.6.1 Displaying Object DependenciesThe 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_totals23.6.2 Displaying Information About Stored ObjectsThe USER_OBJECTS view contains the following key information about an object: OBJECT_NAMEName of the objectOBJECT_TYPEType of the objectSTATUSStatus of the object: VALID or INVALIDHere 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 OFFThe 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 VALIDNotice 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 OFF23.6.3 Analyzing the Size of PL/SQL CodeIn 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 OFFBy 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 3056I 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 CodeUsing 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_atomicsNOTE: 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 NumberAs 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_inThe name of the stored objectline_inThe line number of the line you wish to retrieve (default value is 1)type_inThe 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 VARCHAR2Here, 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 DatabaseIf 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 IDLINEThe line numberSOURCEThe 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. 23.5 Managing Stored Objects with SQL*Plus23.7 Encrypting Stored Code Copyright (c) 2000 O'Reilly & Associates. All rights reserved.