Accessing data dictionary views from PL/SQL

27 Nov

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 */
 l_cnt number;
 select count(*) into l_cnt from v$sql;

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:

 procedure p
 l_cnt number;
 select count(*) into l_cnt from v$sql;

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.

5 Responses to “Accessing data dictionary views from PL/SQL”

  1. Alex November 27, 2012 at 10:26 am #

    Thanks, it’s really useful thing!
    By the way, do you think this is normal behaviour or a bug?

    • savvinov November 27, 2012 at 11:21 am #

      Hi Alex,

      good question — I think it has more than one answer. On one hand I found many experts providing excuses for behavior, on the other hand I personally don’t find most of these excuses convincing (especially considering how easy it is to bypass this restriction).

  2. Rene November 27, 2012 at 11:07 am #

    You can also consider this:

    create or replace procedure p2
    /* but this will NOT fail with ORA-942 */
    l_cnt number;
    execute immediate ‘select count(*) from v$sql’ into l_cnt;

    • savvinov November 27, 2012 at 11:24 am #

      Thanks Rene, that is a very nice trick as well. However in my particular case I have a relatively large package using V$ views that I want to run on a database where I have access to this views via SELECT_CATALOG_ROLE. It takes me only a few seconds to change the package into an anonymous block, but it would take me much longer to change all static queries against V$ views to dynamic SQL.

      Although if you only have one or two such statement then yours is the way to go (would probably require authid current_user).


  1. The Responsibilities of Database Developers and Consultants | Top Apprentice Blog - December 1, 2012

    […] 97 database how to split the copyAccessing data dictionary views from PL/SQL /*Is […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

Join 300 other followers

%d bloggers like this: