Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Trouble with stored proc
Folks,
I have an application SPR on my Oracle 7.3 database, and it has access to a number of tables in a different schema called ORG. When I access one particular table in ORG in SQL*Plus by issuing a "SELECT * FROM ORG.SOMETABLE", everything works fine.
However, if I try to create a stored procedure MYSPROC in SPR that access the table in ORG and selects one row, I get an error saying "PLS-00201: ORG.SOMETABLE must be declared". If I have a copy of the ORG-table available in my local SPR schema, everything works like a charm.
The stored proc looks like this:
create procedure MYSPROC (KeyID IN NUMBER, SomeValue OUT VARCHAR2)
as
begin
select SomeCharField
into SomeValue
from org.sometable
where primarykey = KeyID;
end;
What am I missing?? Isn't it possible to access a table in a different schema from within a stored procedure? Any hints are most welcome!
Marc