Re: Trouble with stored proc

Marc Scheuner wrote in message <347539cd.1886412_at_news.bernoise.ch>...
>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
>
>======================================================================
>[ Marc Scheuner, marc.scheuner_at_berner.ch CH-3001 BERNE, Switzerland ]
>======================================================================

The problem here is to do with grants given via a role. If you have been granted select on ORG.SOMETABLE via a role, then you may NOT access it through a stored procedure; you will have to grant select on the table explicitly to the procedure's owner. This also applies to triggers.

In summary: the only database objects available within a trigger or stored procedure, are those owned by the owner of the trigger or stored procedure, OR those where access has been explicitly granted to the owner.

Hope this helps.

Ian. Received on Fri Nov 21 1997 - 00:00:00 CST