Compile-time warnings

This entry is part 12 of 13 in the series New Pl/Sql features 10g

Most programmers tend to think of a compilation as a final check. If the code compiles it’s ok. Unfortunately compilation checks syntax and dependencies and more, but it does not prevent you from making whooping mistakes like omitting the return clause from a function.

With 10g Oracle introduced a new PL/SQL compilation instrument: compile time warnings.

Main features

  • Oracle can now issue a warning when compiling your PL/SQL.
  • You can selectively enable this behaviour using the initialization parameter PLSQL_WARNINGS
  • You can adjust settings using either alter session statements or the new package DBMS_WARNING

PL/SQL warning messages are divided into categories, so that you can suppress or display groups of similar warnings during compilation. The categories are:

  • SEVERE: Messages for conditions that might cause unexpected behaviour or wrong results, such as aliasing problems with parameters.
  • PERFORMANCE: Messages for conditions that might cause performance problems.
  • INFORMATIONAL: Messages for conditions that do not have an effect on performance or correctness, but that you might want to change to make the code more maintainable, such as unreachable code that can never be executed.

Let’s demonstrate: I want to get things done quickly, which leaves opportunity for mistakes. I’ll compile a function with some really important code to fix a problem in production. In a hurry to fix production I skipped proper testing of my new function – it compiled so it’s good code, what can go wrong? – I am in for an embarrassing code release. In my hurry I forgot to include a return statement, so no matter what, my function will always return null. Oops!!

This could have been prevented using warnings. Let’s look the same example again but this time with warnings enabled. Instead of simply creating the function Oracle now gives me a distinct messages telling me the function compiled, but there might be something wrong with it. So I’m now triggered to review the warnings. In SQL plus this is done using SHOW ERRORS. In tools like SQL developer I can simply enable warning messages in preferences.

This clearly states I forgot to include the return statement. So I can now quickly adjust my function and recompile. (While I’m at it I might even choose to do some proper testwork afterall, since obviously I can make mistakes).

So there you go. PL/SQL warnings saving the day!

Series Navigation<< Quoting Mechanism for String LiteralsImproved Overloading on 10g >>

Posted in Pl/Sql Tagged with: , ,

Leave a Reply