Re: Don't understand why this fails.

>
> There are actually two logins, and in the database creation
> scripts login 1 "grants" rights to login 2.
>

And what kind of rights does login1 grant to login2? Are they direct rights to access some objects in login1 schema (they should be if you want PL/SQL programs under login2 to see objects in login1 schema - any roles assigned to login2 which may grant him additional rights are disabled for PL/SQL)?

For example, login1 creates a table and issues a grant like this:

grant select on mytable to login2;

In this case, PL/SQL programs under login2 will be able to select from login1.mytable. However, if you have a role which receives this grant and you then grant this role to login2, PL/SQL programs under login2 will not be able to see login1.mytable since any roles login2 has are disabled when PL/SQL program runs.

Also note that login2 should specify table owner prefix when accessing "foreign" objects for which it has no synonyms, either private or public: it will see login1.mytable, but won't see just mytable if there's no synonym linking this name to actual object.

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.