Accessing data dictionary views from PL/SQL

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.