Home arrow Oracle arrow Working With PL/SQL Code

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.

TABLE OF CONTENTS:
  1. Working With PL/SQL Code
  2. Synchronizing Code with Packaged Constants
  3. Working with Postprocessed Code
  4. Testing PL/SQL Programs
By: O'Reilly Media
Rating: starstarstarstarstar / 9
November 29, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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-expression
 
$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 compilation, 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 $THEN
         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_compilation
      $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



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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- 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: