Home arrow Oracle arrow Page 4 - Compiling PL/SQL Code for an Oracle Database

How to turn on compile-time warnings - Oracle

In this third part of a nine-part series on managing PL/SQL code, you'll learn how to compile PL/SQL source code, how to use the optimizing compiler, and more. 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. Compiling PL/SQL Code for an Oracle Database
  2. Interpreted Versus Native Compilation Mode
  3. Using the Optimizing Compiler and Compile-Time Warnings
  4. How to turn on compile-time warnings
By: O'Reilly Media
Rating: starstarstarstarstar / 9
November 01, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Oracle allows you to turn compile-time warnings on and off, and also to specify the type of warnings that interest you. There are three categories of warnings:

Severe
  
Conditions that could cause unexpected behavior or
   actual wrong results, such as aliasing problems with
   parameters

Performance
  
Conditions that could cause performance problems,
   such as passing a VARCHAR2 value to a NUMBER
   column in an UPDATE statement

Informational
  
Conditions that do not affect performance or
   correctness, but that you might want to change to
   make the code more maintainable

Oracle lets you enable/disable compile-time warnings for a specific category, for all categories, and even for specific, individual warnings. You can do this with either the ALTER DDL command or the DBMS_WARNING built-in package.

To turn on compile-time warnings in your system as a whole, issue this command:

  ALTER SYSTEM SET PLSQL_WARNINGS='string'

The following command, for example, turns on compile-time warnings in your system for all categories:

  ALTER SYSTEM SET PLSQL_WARNINGS='ENABLE:ALL';

This is a useful setting to have in place during development because it will catch the largest number of potential issues in your code.

To turn on compile-time warnings in your session for severe problems only, issue this command:

  ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE';

And if you want to alter compile-time warnings settings for a particular, already-compiled program, you can issue a command like this:

  ALTER PROCEDURE hello COMPILE PLSQL_WARNINGS='ENABLE:ALL' REUSE SETTINGS;

Make sure to include REUSE SETTINGS to make sure that all other settings (such as the optimization level) are not affected by the ALTER command.

You can tweak your settings with a very high level of granularity by combining different options. For example, suppose that I want to see all performance-related issues, that I will not concern myself with server issues for the moment, and that I would like the compiler to treat PLW-05005: function exited without a RETURN as a compile error. I would then issue this command:

  ALTER SESSION SET PLSQL_WARNINGS=
   
'DISABLE:SEVERE'
  
,'ENABLE:PERFORMANCE'
  
,'ERROR:05005';

I especially like this “treat as error” option. Consider the PLW-05005: function returns without value warning. If I leave PLW-05005 simply as a warning, then when I compile my no_return function, shown below, the program does compile, and I can use it in my application.

  SQL> CREATE OR REPLACE FUNCTION no_return
    2     RETURN VARCHAR2
    3  AS
    4  BEGIN
    5     DBMS_OUTPUT.PUT_LINE (
    6      
 'Here I am, here I stay');
    7
  END no_return;
    8  /

  SP2-0806: Function created with compilation warnings

  SQL> sho err
 
Errors for FUNCTION NO_RETURN:
 

  LINE/COL ERROR 
  -------- ---------------------------------
  1/1      PLW-05005: function NO_RETURN
           returns without value at line 7

If I now alter the treatment of that error with the ALTER SESSION command shown above and then recompile no_return, the compiler stops me in my tracks:

  Warning: Procedure altered with compilation errors

By the way, I could also change the settings for that particular program only, to flag this warning as a “hard” error with a command like this:

  ALTER PROCEDURE no_return COMPILE PLSQL_WARNINGS = 'error:6002' REUSE SETTINGS
  /

This ability to treat a warning as an error did not work in 10.1.0.2; this program was fixed in Oracle Database 10g Release 2 and is reported to be back-ported to 10.1.0.3.

You can, in each of these variations of the ALTER command, also specify ALL as a quick and easy way to refer to all compile-time warnings categories, as in:

  ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

Oracle also provides the DBMS_WARNING package, which provides the same capabilities to set and change compile-time warning settings through a PL/SQL API. DBMS_WARNING also goes beyond the ALTER command, allowing you to make changes to those warning controls that you care about while leaving all the others intact. You can also easily restore the original settings when you’re done.

DBMS_WARNING was designed to be used in install scripts in which you might need to disable a certain warning, or treat a warning as an error, for individual program units being compiled. You might not have any control over the scripts surrounding those for which you are responsible. Each script’s author should be able to set the warning settings he wants, while inheriting a broader set of settings from a more global scope.

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