Managing PL/SQL Code

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.

Writing the code for an application is just one step toward putting that application into production and then maintaining the code base. It is not possible within the scope of this book to fully address the entire life cycle of application design, development, and deployment. We do have room, however, to offer some ideas and advice about the following topics:

Managing and analyzing code in the database

When you compile PL/SQL programs, the source code is loaded into the data dictionary in a variety of forms (the text of the code, dependency relationships, parameter information, etc.). You can therefore use SQL to query these dictionary views to help you manage your code base.

Using native compilation

Beginning with Oracle9i Database, PL/SQL source code may optionally be compiled into native object code that is linked into Oracle. Native compilation can noticeably improve overall application performance (its impact is felt in
compute-intensive programs, but does not affect SQL performance).

Using the optimizing compiler and compile-time warnings

Oracle Database 10g Release 1 added significant new and transparent capabilities to the PL/SQL compiler. The compiler will now automatically optimize your code, often resulting in substantial improvements in performance. In addition, the compiler will provide warnings about your code that will help you improve its readability, performance, and/or functionality.

Testing PL/SQL programs

This section offers suggestions for PL/SQL program testing based on the open source unit-testing framework, utPLSQL.

Debugging PL/SQL programs

Many development tools now offer graphical debuggers based on Oracle’s DBMS_DEBUG API. These provide the most powerful way to debug programs, but they are still just a small part of the overall debugging process. This section also discusses program tracing and explores some of the techniques and (dare I say) philosophical approaches you should utilize to debug effectively.

Tuning PL/SQL programs

This section offers a roundup of some of the more useful and generally applicable tuning tips, along with instructions for how you can analyze your program’s execution with built-in profiling and tracing utilities.

Protecting stored code

Oracle offers a way to “wrap” source code so that confidential and proprietary information can be hidden from prying eyes. This feature is most useful to vendors who sell applications based on PL/SQL stored code.

{mospagebreak title=Managing Code in the Database}

When you CREATE OR REPLACE a PL/SQL program, the source code for that program, along with other representations of that software, is stored in the database itself and exposed through a wide range of data dictionary views. This is a tremendous advantage for two key reasons:

Information about that code is available to you via the SQL language

I can write queries and even entire PL/SQL programs to read the contents of these data dictionary views and obtain lots of fascinating and useful information about my code base.

The database manages dependencies between your stored objects

For example, if a stored function relies on a certain table, and that table’s structure is changed, the status of that function is automatically set to INVALID. Recompilation then takes place automatically when someone tries to execute that function.

This SQL interface to your code base allows you to manage your code repository—running analyses on your code, documenting what has been written and changed, and so on. The following sections introduce you to some of the most commonly accessed sources of information in the data dictionary.

Data Dictionary Views for PL/SQL Programmers

The Oracle data dictionary is a jungle—lushly full of incredible information, but often with less than clear pathways to your destination. There are hundreds of views built on hundreds of tables, many complex interrelationships, special codes, and, all too often, nonoptimized view definitions. A subset of this multitude is particularly handy to PL/SQL developers; we will take a closer look at the key views in a moment. First, it is important to know that there are three types or levels of data dictionary views:


Views that show information about the database objects owned by the currently connected schema.


Views that show information about all of the database objects to which the currently connected schema has access (either because it owns them or because it has been granted access to them). Generally they have the same columns as the corresponding USER view, with the addition of an OWNER column in the ALL views.


Views that show information about all the objects in the database. Generally the same columns as the corresponding ALL view.

We will work with the USER views in this chapter; you can easily modify any scripts and techniques to work with the ALL views by adding an OWNER column to your logic. The following are some views a PL/SQL developer is most likely to find useful:


The dependencies to and from objects you own. This view is mostly used by Oracle to mark objects INVALID when necessary, and also by IDEs to display the dependency information in their object browsers.


The current set of errors for all stored objects you own. This view is accessed by the SHOW ERRORS SQL*Plus command, described in Chapter 2. You can, however, write your own queries against it as well.


The objects you own. You can, for instance, use this view to see if an object is marked INVALID, find all the packages that have “EMP” in their names, etc.


The size of the objects you own. Actually, this view will show you the source, parsed, and compile sizes for your code. Use it to identify the large programs in your environment, good candidates for pinning into the SGA.


(Introduced in Oracle Database 10g Release 1) Information about the characteristics of a PL/SQL object that can be modified through the ALTER and SET DDL commands, such as the optimization level, debug settings, and more.


(Introduced in Oracle9i Database Release 1) Information about stored programs, such as the AUTHID setting, whether the program was defined as DETERMINISTIC, and so on.


The text source code for all objects you own (in Oracle9i Database and above, including database triggers and Java source). This is a very handy view, because you can run all sorts of analysis of the source code against it using SQL and, in particular, Oracle Text.


The database triggers you own, and any columns identified with the triggers. You can write programs against this view to enable or disable triggers for a particular table.


The arguments (parameters) in all the procedures and functions in your schema.

You can view the structures of each of these views either with a DESCRIBE command in SQL*Plus or by referring to the appropriate Oracle documentation. The following sections provide some examples of the ways you can use these views.

{mospagebreak title=Display information about stored objects}

The USER_OBJECTS view contains the following key information about an object:

Name of the object

Type of the object (e.g., ‘PACKAGE’, ‘FUNCTION’,

Status of the object: VALID or INVALID

Timestamp indicating the last time that this object
   was changed.

The following SQL*Plus script displays the status of PL/SQL code objects:

  /* File on web: psobj.sql * /
  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 (
   ORDER BY object_type, status, object_name

The output from this script file contains the following list:

  ————  ————–    ———

                EXCHDLR_PKG       VALID

Notice that a two of my modules are marked as INVALID. See the section “Recom piling Invalid Code” for more details on the significance of this setting and how you can change it to VALID.

{mospagebreak title=Display and search 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?     Typ e
  —————– ——— —-
  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 sophisti cated. 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.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye