Oracle
  Home arrow Oracle arrow Working With PL/SQL Code
Dev Shed Forums 
Administration  
Apache  
BrainDump  
DHTML  
Flash  
Java  
JavaScript  
Multimedia  
MySQL  
Oracle  
Perl  
PHP  
Practices  
Python  
Reviews  
Security  
Style-Sheets  
Web Services  
XML  
Zend  
Zope  
Forums Sitemap 
IBM® developerWorks 
Dedicated Servers 
E-Commerce Hosting 
Linux Web Hosting 
Managed Hosting 
Small Business Hosting 
Download TestComplete 
VPS Hosting 
Weekly Newsletter

 
Developer Updates  
Free Website Content 
IBM Rational Software Development Conference
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
ORACLE

Working With PL/SQL Code
By: O'Reilly Media
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 3 stars3 stars3 stars3 stars3 stars / 5
    2007-11-29

    Table of Contents:
  • Working With PL/SQL Code
  • Synchronizing Code with Packaged Constants
  • Working with Postprocessed Code
  • Testing PL/SQL Programs

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
     
    ADVERTISEMENT

    Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here

    Working With PL/SQL Code
    (Page 1 of 4 )

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


       · This article is an excerpt from the book "Oracle PL/SQL Programming, Fourth...
     

    Buy this book now. 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). Check it out today at your favorite bookstore. Buy this book now.

       

    ORACLE ARTICLES

    - Tuning PL/SQL Code
    - Debugging PL/SQL Code
    - Testing PL/SQL Code
    - Working With PL/SQL Code
    - Conditional Compilation for Oracle Database ...
    - Compile-Time Warnings for Oracle DB 10g
    - Compiling PL/SQL Code for an Oracle Database
    - Troubleshooting PL/SQL Code
    - Managing PL/SQL Code
    - Data Manipulation and More for HTML DB Appli...
    - Oracle Database Fundamentals
    - Adding Processes to HTML DB Applications
    - Adding Computations, Processes, and Validati...
    - Sub-templates and More with Oracle HTML DB
    - Focusing on Templates in Oracle HTML DB

     
    Accelerating Trading Partner Performance
     
    Competing on Analytics
     
    Cost Effective Scaling with Virtualization and Coyote Point Systems
     
    Five Checkpoints to Implementing IP Telephony
     
    Hosted Email Security: Staying Ahead of New Threats
     




    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway