Obsessed with Oracle PL/SQL

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

This question was posted today on the OTN SQL-PL/SQL Forum: Is there a way, after compilation, to detect what level of optimization was used? Perhaps you have something already in production, and you just want to find some candidates for recompile (but not necessarily any changes in the source) with increased optimization level. The answer is: Yes! Just run a query against the ALL_PLSQL_OBJECT_SETTINGS data dictionary view (or the USER_PLSQL_OBJECT_SETTINGS, to see information only about program units you own). For a given schema and object name, the following information is provided through this view: So suppose you need to identify any program units that may have been accidentally compiled with an optimization level below 2 (the default, aggressive optimization, which can only be improved upon by level 3, which turns on inlining of subprograms globally). No problem! SELECT * FROM user_plsql_object_settings p WHERE p.plsql_optimize_level < 2 Or how about: for w...

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