Working With PL/SQL Code

In this sixth part of a nine-part series on managing PL/SQL code, you will learn how to synchronize code with packaged constants, how to work with post-processed code, and more. This article 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.

The $IF Directive

Use the selection directive, implemented through the $IF statement, to direct the conditional compilation step in the preprocessor. Here is the general syntax of this directive:

  $IF Boolean-expressio n
 
$THEN
    
code-fragment
 
[ $ELSEIF Boolean-expression
 
$THEN
    
code-fragment]
  [ $ELSE 
     
code-fragment]
  $END

where Boolean-expression is a static expression (it can be evaluated at the time of compilation) that evaluates to TRUE, FALSE, or NULL The code-fragment can be any set of PL/SQL statements, which will then be passed to the compiler for compila tion, as directed by the expression evaluations.

Static expressions can be constructed from any of the following elements:

  1. Boolean, PLS_INTEGER, and NULL literals, plus combinations of these literals.
  2. Boolean, PLS_INTEGER, and VARCHAR2 static expressions.
  3. Inquiry directives: identifiers prefixed with $$. These directives can be provided by Oracle (e.g., $$PLSQL_OPTIMIZE_LEVEL; the full list is provided in the earlier section “The Optimizing Compiler”) or set via the PLSQL_CCFLAGS compilation parameter (also explained earlier).
  4. Static constants defined in a PL/SQL package.
  5. It can include most comparison operations (>, <, =, <> are fine, but you cannot use an IN expression), logical Boolean operations such as AND and OR, concatenations of static character expressions, and tests for NULL.

A static expression may not contain calls to procedures or functions that require execution; they cannot be evaluated during compilation and therefore will render invalid the expression within the $IF directive. You will get a compile error as follows:

  PLS-00174: a static boolean expression must be used

Here are examples of static expressions in $IF directives:

  • If the user-defined inquiry directive controlling debugging is not null, then initialize the debug subsystem:

      $IF $$app_debug_level IS NOT NULL $THE N
         debug_pkg.initialize;
      $END 
  • Check the value of a user-defined package constant along with the optimization level:

      $IF $$PLSQL_OPTIMIZE_LEVEL = 2 AND appdef_pkg.long_compilatio n
      $THEN
         $ERROR ‘Do not use optimization level 2 for this program!’
      $END

String literals and concatenations of strings are allowed only in the $ERROR directive; they may not appear in the $IF directive.

The $ERROR Directive

Use the $ERROR directive to cause the current compilation to fail and return the error message provided. The syntax of this directive is:

  $ERROR VARCHAR2-expression $END

Suppose that I need to set the optimization level for a particular program unit to 1, so that compilation time will be improved. In the following example, I use the $$ inquiry directive to check the value of the optimization level from the compilation environment. I then raise an error with the $ERROR directive as necessary.

  /* File on web: cc_opt_level_check.sql */
  SQL> CREATE OR REPLACE PROCEDURE long_compilation
   
IS
  
3  BEGIN
  
4  $IF $$plsql_optimize_level != 1
  
$THEN
  
6     $error ‘This program must be compiled with optimization level = 1′ $end
  
$END
  
8     NULL;
  
END long_compilation;
  10  /

Warning: Procedure created with compilation errors. 

  SQL> SHOW ERRORS
  Errors for PROCEDURE LONG_COMPILATION:

  LINE/COL ERROR
  ——– ———————————  6/4      PLS-00179: $ERROR: This program
           must be compiled with
           optimization level = 1

{mospagebreak title=Synchronizing Code with Packaged Constants}

Use of packaged constants within a selection directive allows you to easily synchronize multiple program units around a specific conditional compilation setting. This is possible because Oracle’s automatic dependency management is applied to selection directives. In other words, if program unit PROG contains a selection directive that references package PKG, then PROG is marked as dependent on PKG. When the specification of PKG is recompiled, all program units using the packaged constant are marked invalid and must be recompiled.

Suppose I want to use conditional compilation to automatically include or exclude debugging and tracing logic in my code base. I define a package specification to hold the required constants:

  /* File on web: cc_debug.pks * /
  CREATE OR REPLACE PACKAGE cc_debug
  IS

     debug_active CONSTANT BOOLEAN := TRUE;
    
trace_level CONSTANT PLS_INTEGER := 10;
  END cc_debug;
  /

I then use these constants in procedure calc_totals:

  CREATE OR REPLACE PROCEDURE calc_totals
  IS
  BEGIN
  $IF cc_debug.debug_active AND cc_debug.trace_level > 5 $THEN
    
log_info (…);
  $END
    

  END calc_totals;
  /

During development, the debug_active constant is initialized to TRUE. When it is time to move the code to production, I change the flag to FALSE and recompile the package. The calc_totals program and all other programs with similar selection directives are marked invalid and must then be recompiled.

Program-Specific Settings with Inquiry Directives

Packaged constants are useful for coordinating settings across multiple program units. Inquiry directives, drawn from the compilation settings of individual programs, are a better fit when you need different settings applied to different programs.

Once you have compiled a program with a particular set of values, it will retain those values until the next compilation (either from a file or a simple recompilation using the ALTER…COMPILE statement). Furthermore, a program is guaranteed to be recompiled with the same postprocessed source as was selected at the time of the previous compilation if all of the following conditions are TRUE:

  1. None of the conditional compilation directives refer to package constants. Instead, they rely only on inquiry directives.
  2. When the program is recompiled, the REUSE SETTINGS clause is used and the PLSQL_CCFLAGS parameter isn’t included in the ALTER…COMPILE command.

This capability is demonstrated by the cc_reuse_settings.sql script, whose output is shown below. I first set the value of app_debug to TRUE and then compile a program with that setting, A query against USER_PLSQL_OBJECT_SETTINGS shows that this value is now associated with the program unit:

  /* File on web: cc_reuse_settings.sql */

  SQL> ALTER SESSION SET plsql_ccflags = ‘app_debug:TRUE';

  SQL> CREATE OR REPLACE PROCEDURE test_ccflags
    2  IS
    3  BEGIN
    4     NULL;
    5  END test_ccflags;
    6  /

  SQL> SELECT name, plsql_ccflags
    2    FROM user_plsql_object_settings
    3  WHERE NAME LIKE ‘%CCFLAGS%';

  NAME               PLSQL_CCFLAGS
  —————— ———————–
  TEST_CCFLAGS       app_debug:TRUE

I now alter the session, setting $$app_debug to evaluate to FALSE. I compile a new program with this setting:

  SQL> ALTER SESSION SET plsql_ccflags = ‘app_debug:FALSE';

  SQL> CREATE OR REPLACE PROCEDURE test_ccflags_new
  
IS
  
BEGIN
  
4     NULL;
  
END test_ccflags_new;
  
6  /

Then I recompile my existing program with REUSE SETTINGS:

  SQL> ALTER PROCEDURE test_ccflags COMPILE REUSE SETTINGS;

A query against the data dictionary view now reveals that my settings are different for each program:

  SQL> SELECT name, plsql_ccflags
   
2    
FROM user_plsql_object_settings
   3   WHERE NAME LIKE ‘%CCFLAGS%';

  NAME                 PLSQL_CCFLAGS
  ——————– ———————
  TEST_CCFLAGS         app_debug:TRUE 
  TEST_CCFLAGS_NEW     app_debug:FALSE

{mospagebreak title=Working with Postprocessed Code}

You can use the DBMS_PREPROCESSOR package to display or retrieve the source text of your program in its postprocessed form. DBMS_PREPROCESSOR offers two programs, overloaded to allow you to specify the object of interest in various ways, as well as to work with individual strings and collections:

DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE

Retrieves the postprocessed source and then displays it with the function DBMS_OUTPUT.PUTLINE.

DBMS_PREPROCESSOR.GET_POST_PROCESSED_SOURCE

Returns the postprocessed source as either a single string or a collection of strings.

When working with the collection version of either of these programs, you will need to declare that collection based on the following package-defined collection:

  TYPE DBMS_PREPROCESSOR.source_lines_t IS TABLE OF VARCHAR2(32767)
     INDEX BY BINARY_INTEGER;

The following sequence demonstrates the capability of these programs. I compile a very small program with a selection directive based on the optimization level. I then display the postprocessed code, and it shows the correct branch of the $IF statement.

  /* File on web: cc_postprocessor.sql  
  CREATE OR REPLACE PROCEDURE post_processed
  IS
  BEGIN
  $IF $$PLSQL_OPTIMIZE_LEVEL = 1
  $THEN 
     
– Slow and easy
   
NULL;
  $ELSE
    
– Fast and modern and easy
    
NULL;
 
$END

  END post_processed;
  /

  SQL> BEGIN
   
2     
DBMS_PREPROCESSOR. PRINT_POST_PROCESSED_SOURCE (
   3         
‘PROCEDURE’, USER, ‘POST_PROCESSED’);
   4  END;
   5
  /

  PROCEDURE post_processed
  IS
  BEGIN
  — Fast and modern and easy
  NULL;
  END post_processed;

In the following block, I use the “get” function to retrieve the postprocessed code, and then display it using DBMS_OUTPUT.PUT_LINE:

  DECLARE
     l_postproc_code      DBMS_PREPROCESSOR. SOURCE_LINES_T;
     l_row                PLS_INTEGER;
  BEGIN
    
l_postproc_code := 
   DBMS_PREPROCESSOR.GET_POST_PROCESSED_SOURCE (
      ‘PROCEDURE’, USER, ‘POST_PROCESSED’);
     l_row := l_postproc_code.FIRST;

     WHILE (l_row IS NOT NULL)
     LOOP
       
DBMS_OUTPUT.put_line ( LPAD (l_row, 3)
                             || ‘ – ‘
                             || rtrim ( l_postproc_code (l_row),chr(10))
                           
);
        l_row := l_postproc_code.NEXT (l_row);
     END LOOP;
  END;
  /

Conditional compilation opens up all sorts of possibilities for PL/SQL developers and application administrators. And its usefulness only increases as new versions of Oracle are released and the DBMS_DB_VERSION constants can be put to full use, allowing us to take full advantage of each version’s unique PL/SQL features.

{mospagebreak title=Testing PL/SQL Programs}

I get great satisfaction out of creating new things, and that is one of the reasons I so enjoy writing software. I love to take an interesting idea or challenge, and then come up with a way of using the PL/SQL language to meet that challenge.

I have to admit, though, that I don’t really like having to take the time to test my software (nor do I like to write documentation for it). I do it, but I don’t really do enough of it. And I have this funny feeling that I am not alone. The overwhelming reality is that developers generally perform an inadequate number of inadequate tests and figure that if the users don’t find a bug, there is no bug. Why does this happen? Let me count the ways…

The psychology of success and failure

We are so focused on getting our code to work correctly that we generally shy away from bad news—or from taking the chance of getting bad news. Better to do some cursory testing, confirm that everything seems to be working OK, and then wait for others to find bugs, if there are any (as if there were any doubt).

Deadline pressures

Hey, it’s Internet time! Time to market determines all. We need everything yesterday, so let’s release pre-beta software as production and let our users test/suffer through our applications.

Management’s lack of understanding

IT management is notorious for not really understanding the software development process. If we aren’t given the time and authority to write (and I mean “write” in the broadest sense, including testing, documentation, refinement, etc.) code properly, we will always end up with buggy junk that no one wants to admit ownership of.

Overhead of setting up and running tests

If it’s a big deal to write and run tests, they won’t get done. We’ll decide that we don’t have time; after all, there is always something else to work on. One consequence of this is that more and more of the testing is handed over to the QA department, if there is one. That transfer of responsibility is, on the one hand, positive. Professional quality assurance professionals can have a tremendous impact on application quality. Yet developers must take and exercise responsibility for unit testing their own code; otherwise, the testing/QA process is much more frustrating and extended.

The bottom line is that our code almost universally needs more testing. I recently spent a fair amount of time thinking about how to improve my testing procedures. I studied test frameworks developed by other programmers who work primarily with object-oriented languages. An obsessive coder, I then proceeded to construct my own framework for unit testing PL/SQL programs, which I named utPLSQL, an open source project that is being used by developers around the world. It is complemented by Ounit, a graphical interface to utPLSQL. Let’s take a look at how these tools can help.

Please check back next week for the continuation of this article.

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

antalya escort bayan antalya escort bayan Antalya escort diyarbakir escort