08Dec
By: Steven Feuerstein On: December 8, 2021 In: APEX Developer Solutions, Feuertips Comments: 0

Well, that’s what it should say, anyway.

The most important job of a compiler is to transform code written in our high-level (comprehensible to humans) languages into bits and bytes that direct the hardware to do our bidding.

But ever since Oracle Database 10g, the PL/SQL compiler also:

  • Automatically optimizes our code to improve performance
  • Makes suggestions on improving the performance, correctness, and maintainability of our code.

Well, that’s what it can do, if you let it.

By default, the PL/SQL optimization level is set to 2, which means it performs almost all available optimizations. If you up that level to 3, you also benefit from the inlining of subprograms. But very few organizations change the level from 2, so chances are PL/SQL is automatically optimizing your code’s performance.

Most developers do not, however, take advantage of the compiler’s ability to make those improvement suggestions. That’s because this feature – which is called compile-time warnings – is turned off by default. Which I think was a mistake. But no one asked me.

What the compiler can detect automatically about your code is nothing short of amazing. Revelations (can) include:

  • Lines of code that are unreachable (cannot possibly be executed)
  • Functions that will not return a value when executed
  • Run-time errors (identified at the time of compilation), like dividing a number by 0 or trying to stuff “abcde” into a varchar2(3) variable

You can even ask the compiler to treat really serious warnings as actual errors so that your code will not compile and cannot be run until you address those issues.

The bottom line?

You should always have compile-time warnings enabled in your session. The best way to do this is to set up your IDE or code editor so that it automatically executes a script to turn them on.

In SQL Developer, this is available in Preferences / Database / PL/SQL Compiler.

If you want to do something more nuanced, like “Change these three specific warnings to errors, all others simply enabled” then you will need to execute an alter session statement along these lines:

alter session set plsql_warnings =

   'error:SEVERE',

   'enable:PERFORMANCE',

   'disable:INFORMATIONAL', 

   'error:5005',

   'error:6002'

Rather than show you lots of examples in this post, I leave it to you to explore the resources below.

So: no more excuses. You’ve been warned!

Resources

Go exploring! Check out the PL/SQL documentation on the warnings feature. Explore all the warnings (PLW-NNNNN) in the Error Messages manual. Learn how to turn on warnings in SQL Developer, run the LiveSQL scripts that demonstrate the power of the warnings analysis.

Compile-time Warnings  (doc)

Error Messages: PL/SQL warnings  (doc)

PLSQL Warning Messages in Oracle SQL Developer (Jeff Smith blog)

Three tips for getting started right with Oracle Database development (SF blog)

Lint Checkers for PL/SQL (SF blog)

Oracle LiveSQL scripts 

Warning demo scripts (the plw*.sql files in this zip are all demos of compile-time warnings)

Feuertips

Every Wednesday at 11 am ET, I go live on YouTube and Facebook with Michelle Skamene to provide you with a not-to-be-missed PL/SQL tip and fun conversation. Sure, we record it so you can always watch later, but live is so much more rewarding!
Speaking of rewards, Steven finishes each show with a quick quiz to see if you were listening. Participation, of course, is optional!
One lucky quiz answerer will be selected at random to choose from three organizations dedicated to the preservation of our planet and its biodiversity: The Sheldrick Wildlife Trust, The Center for Biological Diversity, and Earthwatch.
Insum will then make a donation of $25 to that group in your name.
What could be better than leveling up your PL/SQL tips and helping one of these worthy organizations? Join us Wednesdays at 11!
Share this:
Share

Leave reply:

Your email address will not be published.