Re: Accessing tables from another Schema

A copy of this was sent to Jens Mayer <jmayer_at_ratundtat.com> (if that email address didn't require changing) On Thu, 06 May 1999 16:44:28 +0200, you wrote:

>Hi folks !
>
>I want to access a table from within a package which was created in
>another schema. This doesn't work, even if I use the schema prefixing
>the table name. If I create the package in the same schema as the table,
>it works fine. Are there any package-specific restrictions in accessing
>objects of another schema ?
>
>Jens

roles are never enabled during the execution of a procedure.

Try this:

SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"

If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.

You probably have the privelege to do what you are trying to do in the procedure via a role. Grant the privelege directly to the owner of the procedure and it'll work.

grant SELECT on <that table> to <OWNER of the package>;

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu May 06 1999 - 11:59:30 CDT