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

Setting compilation environment parameters - 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.

  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



The following information (corresponding to the values in the USER_PLSQL_OBJECT_SETTINGS data dictionary view) is available via inquiry directives:

Debug setting for this compilation unit

   Optimization level for this compilation unit

Compilation mode for the unit

Compilation warnings setting for this compilation unit

   Value set for the NLS length semantics

See the cc_plsql_parameters.sql file on the book’s web site for a demonstration that uses each of these parameters.

Referencing unit name and line number

Oracle implicitly defines two very useful inquiry directives for use in $IF and $ERROR directives:

Name of the compilation unit in which the reference

Line number of the compilation unit where the
   reference appears

You can call DBMS_UTILITY.FORMAT_CALL_STACK and DBMS_UTILITY. FORMAT_ERROR_BACKTRACE to obtain current line numbers, but then you must also parse those strings to find the line number and program unit name. These inquiry directives provide the information more directly. Here is an example:

IF l_balance < 10000
raise_error (
err_name => 'BALANCE TOO LOW'
,failed_in => $$plsql_unit
,failed_on => $$plsql_line

Run cc_line_unit.sql to see a demonstration of using these last two directives.

Note that when $$PLSQL_UNIT is referenced inside a package, it will return the name of the package, not the individual procedure or function within the package.

Using the PLSQL_CCFLAGS parameter

Oracle offers a new initialization parameter, PLSQL_CCFLAGS, that you can use with conditional compilation. Essentially, it allows you to define name-value pairs, and the name can then be referenced as an inquiry directive in your conditional compilation logic. Here is an example:

  ALTER SESSION SET PLSQL_CCFLAGS = 'use_debug:TRUE, trace_level:10';

The flag name can be set to any valid PL/SQL identifier, including reserved words and keywords (the identifier will be prefixed with $$, so there will be no confusion with normal PL/SQL code). The value assigned to the name must be one of the following: TRUE, FALSE, NULL, or a PLS_INTEGER literal.

The PLSQL_CCFLAGS value will be associated with each program that is then compiled in that session. If you want to keep those settings with the program, then future compilations with the ALTER...COMPILE command should include the REUSE SETTINGS clause.

Because you can change the value of this parameter and then compile selected program units, you can easily define different sets of inquiry directives for different programs.

Note that you can refer to a flag that is not defined in PLSQL_CCFLAGS; this flag will evaluate to NULL. If you enable compile-time warnings, this reference to an undefined flag will cause Oracle to report a PLW-06003: unknown inquiry directive warning (unless the source code is wrapped).

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

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


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