Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Trouble with stored proc

Re: Trouble with stored proc

From: <syakobson_at_erols.com>
Date: 1997/11/21
Message-ID: <880160836.9439@dejanews.com>#1/1

Stored procedure has privileges its owner is granted directly, not via role. Most likely SPR is granted SELECTon ORG.SOMETABLE via role. Logon as ORG and issue GRANT SELECT ON SOMETABLE TO SPR;

Solomon Yakobson.

In article <347539cd.1886412_at_news.bernoise.ch>,   marc.scheuner_at_NO.SPAM.PLEASE.CH (Marc Scheuner) wrote:
>
> 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 ]
> ======================================================================

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet
Received on Fri Nov 21 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US