Obsessed with Oracle PL/SQL

Huh, what? Make sure my code fails  to compile? Why would I want to do that. Well, suppose that you had a compute-intensive procedure that ran every hour and benefited greatly from full PL/SQL compiler optimization (level set to 3, to take advantage of subprogram inlining and everything else it does). Next, suppose that somehow as the procedure (newly enhanced, fully tested) was being deployed to production, the optimization level was mistakenly set to 0 or 1. This would cause severe performance problems. So in that case, wouldn't it be nice if you could build a "circuit breaker" into that procedure so that the compiler says "No go" even if the code itself compiles just fine? I think it would be nice - and you can accomplish precisely that with the error directive of the conditional compilation feature of PL/SQL. First, here's the code that demonstrates precisely the scenario outlined above. CREATE OR REPLACE PROCEDURE compute_intensive AUTH...

PL/SQL is a key enabling technology in Oracle Database. You should make sure that you are aware of and take advantage appropriately key features of the PL/SQL language focused on performance. I offer a short list of those features below, along with links to related resources. Most of this is also capture in this  slide deck : Key Performance Features All of these are covered in the slide deck above; the links will take you to the documentation on these features. Click here for the overall section in the doc on performance and optimization. Bulk processing with FORALL and BULK COLLECT The function result cache Optimized execution of user-defined functions from SQL The NOCOPY Hint Automatic compiler optimization But Don't Forget About SQL Optimization Chances are you could take full advantage of all the features listed above and more in PL/SQL, and still end up with a slow application. That's because at the heart of every application built on Oracle Database...

On the one hand, when you test something, you want to keep your test code as simple as possible so that you can focus on the issue you are testing. On the other hand, if you make your code too  simple you might find yourself baffled at the resulting behavior. Why? Because the PL/SQL compiler is just too darned smart. Today, I got a DM on Twitter asking me why the package body below was compiling without any errors, even though he specified that the PLW-06009 warning should be treated as a compile error. The code: ALTER SESSION SET plsql_warnings = 'Error:6009'; CREATE OR REPLACE PACKAGE pkg_test AS PROCEDURE test_job (p_test_parameter IN OUT VARCHAR2); END pkg_test; / CREATE OR REPLACE PACKAGE BODY pkg_test AS PROCEDURE test_job (p_test_parameter IN OUT VARCHAR2) IS BEGIN NULL; EXCEPTION WHEN OTHERS THEN NULL; END test_job; END pkg_test; / Certainly seems like that exception handler allows the OTHERS handler to exit test_job with...

Charles Wetherell, Consulting Member of the PL/SQL development team, was kind enough to offer these insights regarding PL/SQL optimization and native code generation. A PL/SQL programmer asked why PL/SQL native code generation was turned off when the PL/SQL optimization level was set to 1. There are four PL/SQL optimization levels:  0. Esoteric for some long-since-passed compatibility issues with release 9 and before  1. Basic code generation with debugging data created  2. Global optimization  3. Automatic inlining of local procedures Each level builds on the level before. Debugging data is not created above level 1. Generally, native code generation is independent of optimization level. Native code generation is turned off at levels 1 (and 0) because it interferes with debugging. In other words, PL/SQL code compiled at optimization levels 0 and 1 is always interpreted when executed. You should never use level 0. That is a blanket prescription. Certainly no n...

The PL/SQL compiler does more than compile - it also: automatically optimizes your code to run faster offers advice in the form of compile-time warnings to improve the quality and/or performance of your code allows you to conditionally include or exclude portions of your code for compilation That's just fantastic - but it can now and then result in some confusing moments for the Oracle Database developer (well, at least this developer). Recently, I was looking over the warnings I had gotten for a new package I'd written and saw this: Wait - my "procedure" user_goals_cur was removed ? I could tell by the name that it was not a procedure - it was a cursor. So clearly the warning message hasn't been customized to the type of code removed. OK, that's no big deal - I can deal with that.  But when I see the PLW-06006 warning, it has meant that I'd written a nested subprogram in a procedure or function but it was no longer used. It was...