Displaying and Searching Source Code

You should always maintain the source code of your programs in text files (or via a development tool specifically designed to store and manage PL/SQL code outside of the database). When you store 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 is as 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 the object (such as PROCEDURE, FUNCTION, PACKAGE, or PACKAGE BODY), LINE is the line number, and TEXT is the text of the source code.

USER_SOURCE is a very valuable resource for developers. With the right kind of queries, you can do things like:

· Display source code for a given line number

· Validate coding standards

· Identify possible bugs or weaknesses in your source code

Suppose, for example, that we have set as a rule that individual developers should never hardcode one of those application-specific error numbers between -20,999 and -20,000 (such hardcodings can lead to conflicting usages and lots of confusion). I can't stop a developer from writing code like this:

RAISE_APPLICATION_ERROR (-20306, 'Balance too low');

but I can create a package that allows me to identify all the programs that have such a line in them. I call it my"validate standards" package; it is very simple, and its main procedure looks like this:

/* File on web: valstd.pkg */CREATE OR REPLACE PACKAGE BODY valstdIS CURSOR objwith_cur (str IN VARCHAR2) IS SELECT name, text FROM USER_SOURCE WHERE UPPER (text) LIKE '%' || UPPER (str) || '%' AND name != 'VALSTD'; PROCEDURE progwith (str IN VARCHAR2) IS BEGIN FOR prog_rec IN objwith_cur (str) LOOP do.pl (prog_rec.name, prog_rec.text); END LOOP; END;END valstd;

Once this package is compiled into my schema, I can check for usages of -20,NNN numbers with this command:

SQL> EXEC valstd.progwith ('-20')CHECK_BALANCE - RAISE_APPLICATION_ERROR (-20306, 'Balance too low');MY_SESSION - PRAGMA EXCEPTION_INIT(dblink_not_open,-2081);VSESSTAT - CREATE DATE : 1999-07-20

Notice that the third line in my output is not really a problem; it shows up only because I couldn't define my filter narrowly enough.

Another fine use of this package might be to find all declarations using the fixed-length CHAR datatype:

SQL> EXEC valstd.progwith ('-20')

This is a fairly crude analytical tool, but you could certainly make it more sophisticated. You could also have it generate HTML that is then posted on your intranet. You could then run the valstd scripts every Sunday night through a DBMS_JOB-submitted job, and each Monday morning developers could check the intranet for feedback on any fixes needed in their code.

Finally, here is a procedure you can run to identify anystandalone procedures and functions (sorry, it doesn't work for packaged programs) that do not have an exception section. It isn't foolproof (for example, it will not identify itself), but it's better than nothing!

/* File on web: shownoexc.sp */CREATE OR REPLACE PROCEDURE show_no_exc_sectionsIS CURSOR check_for_exc (nm IN VARCHAR2) IS SELECT line FROM user_source WHERE NAME = nm AND INSTR (UPPER (text), 'EXCEPTION') > 0; check_rec check_for_exc%ROWTYPE;BEGIN FOR obj_rec IN (SELECT object_name, object_type FROM user_objects WHERE object_type IN ('PROCEDURE', 'FUNCTION')) LOOP OPEN check_for_exc (obj_rec.object_name); FETCH check_for_exc INTO check_rec; IF check_for_exc%FOUND THEN NULL; ELSE DBMS_OUTPUT.put_line ( obj_rec.object_type || ' ' || obj_rec.object_name || ' does not contain the EXCEPTION keyword.' ); END IF; CLOSE check_for_exc; END LOOP;END;