Home arrow Oracle arrow Page 3 - Conditional Compilation for Oracle Database 10g

The Inquiry Directive - Oracle

In this fifth part of a nine-part series on PL/SQL, you'll learn how conditional compilation lets you compile selected parts of a program when you're working with Oracle Database 10g. 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.

TABLE OF CONTENTS:
  1. Conditional Compilation for Oracle Database 10g
  2. Examples of Conditional Compilation
  3. The Inquiry Directive
  4. Setting compilation environment parameters
By: O'Reilly Media
Rating: starstarstarstarstar / 6
November 15, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

An inquiry directive is a directive that makes an inquiry of the compilation environment. Of course, that doesnít really tell you much. So letís take a closer look at the syntax for inquiry directives and the different sources of information available through the inquiry directive.

The syntax for an inquiry directive is as follows:

  $$identifier

where identifier is a valid PL/SQL identifier that can represent any of the following:

  1. Compilation environment settings: the values found in the USER_PLSQL_OBJECT_SETTINGS data dictionary view
  2. Your own custom-named directive, defined with the ALTER...SET PLSQL_CCFLAGS command, described in a later section
  3. Implicitly defined directives: $$PLSQL_LINE and $$PLSQL_UNIT, providing you with the line number and program name

Inquiry directives are designed for use within conditional compilation clauses, but they can also be used in other places in your PL/SQL code. For example, I can display the current line number in my program with this code:

  DBMS_OUTPUT.PUT_LINE ($$PLSQL_LINE);

I can also use inquiry directives to define and apply application-wide constants in my code. Suppose, for example, that the maximum number of years of data supported in my application is 100. Rather than hardcode this value in my code, I could do the following:

  ALTER SESSION SET PLSQL_CCFLAGS = 'max_years:100';

  CREATE OR REPLACE PROCEDURE work_with_data (num_years_in IN PLS_INTEGER)
  IS
  BEGIN
    
IF num_years_in > $$max_years THEN ...
  END work_with_data;

Even more valuable, I can use inquiry directives in places in my code where a variable is not allowed. Here are two examples:

  DECLARE
     l_big_string VARCHAR2($$MAX_VARCHAR2_SIZE);

     l_default_app_err EXCEPTION;
    
PRAGMA EXCEPTION_INIT (l_default_app_err, $$DEF_APP_ERR_CODE);
  BEGIN

The DBMS_DB_VERSION package

The DBMS_DB_VERSION built-in package offers a set of constants that give you absolute and relative information about the version of your installed database. The constants defined in the Oracle Database 10g Release 2 version of this package are shown in Table 20-2.

Table 20-2. DBMS_DB _VERSION constants

Name of packaged constant

Significance

Value in Oracle Database10g Release 2

DBMS_DB_VERSION.VERSION

 The RDBMS version number, as in 10 for Oracle Database 10g

10

Table 20-2. DBMS_DB _VERSION constants (continued)

Name of packaged constant

Significance

Value in Oracle Database 10g Release 2

DBMS_DB_VERSION.RELEASE

The RDBMS release number, as in 2 for Oracle Data-base 10gRelease 2

2

DBMS_DB_VERSION.VER_LE_9

TRUE if the current version is less than or equal to Oracle9iDatabase

FALSE

DBMS_DB_VERSION.VER_LE_9_1

TRUE if the current version is less than or equal to Oracle9iDatabase Release 1

FALSE

DBMS_DB_VERSION.VER_LE_9_2

TRUE if the current version is less than or equal to Oracle9iDatabase Release 2

FALSE

DBMS_DB_VERSION.VER_LE_10

TRUE if the current version is less than or equal to Oracle Database 10g

TRUE

DBMS_DB_VERSION.VER_LE_10_1

TRUE if the current version is less than or equal to Oracle Database 10g Release 1

FALSE

DBMS_DB_VERSION.VER_LE_10_2

TRUE if the current version is less than or equal to Oracle Database 10gRelease 2

TRUE

While this package was designed for use with conditional compilation, you can, of course, use it for your own purposes.

With each new release of the database, Oracle will add additional constants and will update the values returned by the VERSION and RELEASE constants.

Interestingly, you can write expressions that include references to as-yet undefined constants in the DBMS_DB_VERSION package. As long as they are not evaluated, as in the case below, they will not cause any errors. Here is an example:

  $IF DBMS_DB_VERSION.VER_LE_10_2
  $THEN
    
Use this code.
  $ELSEIF DBMS_DB_VERSION.VER_LE_11
    
This is a placeholder for future.
  $ENDIF



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