A couple of points:
Oracle has bother to document this. A search in the Concepts manual
(which took me all of 38 seconds, I set timing on) found this in the
Introduction to Roles: "..roles are not meant to be used for application
developers, because the privileges to access schema objects within
stored programmatic constructs need to be granted directly".
Further on when discussing roles in the PL/SQL Blocks and Roles section,
there is a discussion on this very subject that describes it very
clearly. "All roles are disabled in any named PL/SQL block (stored
procedure, function or trigger) that executes with definer rights" is
one such quote.
It *IS* clearly and concisely documented in the first (and most obvious)
place I looked !!
It is posting etiquette to at least make some attempt to solve a problem
and search the google archives in case the question has been asked
before. This serves a dual purpose. Firstly, subscribers of the NG don't
have to download the same question again and again and perhaps more
importantly, the poster is very likely to find the answer more quickly
in the archives than waiting for an answer to arrive. A quick search on
this subject (roles procedure privileges) in google found 1,280 hits,
the first 50 of which (all I bothered to check) *all* had the answer to
Don't worry too much if you don't find my joke funny, few do :)
> On Fri, 09 Aug 2002 17:17:44 +1000, Richard Foote
> >Hey, why not make it a competition. The letter which answer's this
> >question the fastest becomes the letter of the year !!
> You may make it into a joke, but is far as I know Oracle never
> bothered to document this not so obvious restriction in it's official
> manuals, and this is in my opinion what makes people stumble over this
> again and again.
< 1K Download