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 ...;
