Re: Accessing another schema from inside a Package/Function

The package must have direct (not via a role) rights to the objects in the other schema.

"Bruce G Wilson" <bgw_at_world.std.com> wrote in message news:GDHKzq.Ip2_at_world.std.com...
> I'm writing have a function that's in a package, and it's executing a
> SELECT that's referencing tables in a different schema. This same
> SELECT works fine if executed directly in SQL*Plus, but when I try to
> create the function/package I'm getting an error saying:
>
> "PLS-00201: identifier 'SCHEMA.TABLENAME' must be declared"
>
> Anyone know what I'm doing wrong here ? Is there some restriction
> on the accessibility of other schemas from inside a package/function ?
> Is there some restriction on the syntax for referencing tables in
> other schemas withing DML statements in functions ?
>
> I'm using Oracle8i, version 8.1.6 on Solaris.
>
> Here's a rough sketch of the code that I'm working with:
>
> CREATE OR REPLACE PACKAGE Report AS
> TYPE ReportResults IS REF CURSOR;
> FUNCTION detailedReport( p_id IN NUMBER )
> return ReportResults;
> END Report;
> /
>
> CREATE OR REPLACE PACKAGE BODY Report AS
> FUNCTION detailedReport( p_id IN NUMBER )
> return ReportResults
> IS
> results ReportResults;
> BEGIN
> OPEN results FOR
> SELECT ...columns...
> FROM SCHEMA.TABLENAME alias,
> WHERE ...
> ORDER BY ...;
> END;
> END Report;
> /
>
> Thanks in advance.
>
> - Bruce
Received on Thu May 17 2001 - 20:31:46 CEST