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
Suppose, for example, that we have set as a rule that individual developers should never hardcode one of those application-specific error numbers between
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:
/* Files on web: valstd.pks, valstd.pkb */
TYPE info_aat IS TABLE OF info_rt
disp_header ('Checking for presence of "' || str || '"');
FOR indx IN info_aa.FIRST .. info_aa.LAST
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
MY_SESSION - PRAGMA EXCEPTION_INIT(dblink_not_open,-2081);
Notice that the second and third lines in my output are not really a problem; they show up only because I couldn’t define my filter narrowly enough.
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.
Please check back next week for the continuation of this article.
blog comments powered by Disqus