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)
Warning demo scripts (the plw*.sql files in this zip are all demos of compile-time warnings)
