Home arrow Oracle arrow Page 4 - Managing PL/SQL Code

Display and search source code - Oracle

Creating the code for an application is just the first step; you must then manage it, and that includes everything from compiling to testing and debugging. This article, the first part in a nine-part series, covers these issues for PL/SQL. It is excerpted from chapter 20 of the book Oracle PL/SQL Programming, Fourth Edition, written by Steven Feuerstein and Bill Pribyl (O'Reilly; ISBN: 0596009771). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

  1. Managing PL/SQL Code
  2. Managing Code in the Database
  3. Display information about stored objects
  4. Display and search source code
By: O'Reilly Media
Rating: starstarstarstarstar / 10
October 18, 2007

print this article



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(4000)


   Is the name of the object

   Is the type of the object (ranging from PL/SQL 
   program units to Java source to trigger source)

   Is the line number

   Is the text of the source code

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

  1. Display source code for a given line number
  2. Validate coding standards
  3. Identify possible bugs or weaknesses in your source code
  4. Look for programming constructs not identifiable from other views

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:

  /* Files on web: valstd.pks, valstd.pkb */
  PROCEDURE progwith (str IN VARCHAR2)
TYPE info_rt IS RECORD (
      NAME  user_source.NAME%TYPE
    , text  user_source.text%TYPE

    TYPE info_aat IS TABLE OF info_rt

    info_aa info_aat;
    SELECT NAME || '-' || line
         , text
      FROM user_source
     WHERE UPPER (text) LIKE '%' || UPPER (str) || '%'
       AND NAME <> 'VALSTD'
       AND NAME <> 'ERRNUMS';

    disp_header ('Checking for presence of "' || str || '"');

    FOR indx IN info_aa.FIRST .. info_aa.LAST
       pl (info_aa (indx).NAME, info_aa (indx).text);
  END progwith;

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') 
  Checking for presence of "-20"

(-20306, 'Balance too low');

  MY_SESSION -   PRAGMA EXCEPTION_INIT(dblink_not_open,-2081);
  VSESSTAT - CREATE DATE   : 1999-07-20

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.

>>> More Oracle Articles          >>> More By O'Reilly Media

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: