Re: Using a PL/SQL Subprogram Procedure.

When you reference the object in an anonymous PL/SQL block, access to the object is resolved based on your current privileges (which can be granted either through roles or directly from user to user). But access granted through roles is "transitory" in the sense that you can potentially change roles, or roles can be modified. If a procedure is to be stored in the database, (remembering that it is executed under the owner's privilege domain), and you have multiple roles, how does the database decide what privilege domain to use (or should it use all of them).

 Actually, the question is retorical. To more or less guarantee that a stored procedure is always valid, the database will only take into account directly granted privileges, as these are always valid regardless of what roles you might currently be running under.

In effect roles come and go, but directly granted privileges are forever :-)

PS. I said earlier "more or less guarantee" because privileges granted directly between users can, of course, be revoked, at which point the stored procedure would become invalid.

Hope this helps explain the difference between an anonymous PL/SQL block and a stored procedure. Received on Sat Jun 08 1996 - 00:00:00 CEST