As most of database developers who have to work with database performance analysis and tuning, I have my own collection of favorite sqlplus scripts for various occasions. Over years, I have turned many of them into PL/SQL code which allows greater flexibility. Imagine, for examples, trying to call a sqlplus script from another sqlplus script with both in and out parameters, or simulating autonomous transactions in sqlplus — obviously, such things are much easier in PL/SQL. Of course, PL/SQL has certain drawbacks of its own, one of them being the rather peculiar way Oracle handles user security. More specifically, you cannot use privileges received via roles inside PL/SQL code. This can be really annoying, especially for performance benchmarking code, which heavily relies on access to data dictionary views, granted via SELECT_CATALOG_ROLE or similar, e.g.:
create user test_privs identified by test_privs; grant connect, select_catalog_role to test_privs; grant resource to test_privs; connect test_privs/test_privs select /* this runs fine */ count(*) from v$sql; create or replace procedure p /* but this fails with ORA-942 */ is l_cnt number; begin select count(*) into l_cnt from v$sql; end; /
I found one way to circumvent this annoying bug/feature by moving procedures and functions to the declare section of an anonymous blocks, i.e. something like:
declare procedure p is l_cnt number; begin select count(*) into l_cnt from v$sql; end; begin p; end; /
Of course what makes this simple trick really useful, is support of local routines in anonymous PL/SQL blocks. Thanks to this nice feature, even a relatively complex package can be rewritten in a couple of minutes. Basically, all you need to do is replace “create or replace package <name> is” with “declare” and copy-paste all global variables from the package spec into the declare section as local variables.