Re: a question for stored procedure

Hi, Marks:

I called oracle support and now I know what is the problem:

basically, if you can access certain tables because your ROLE allows you, you will be fine in sqlplus and pl/sql to access them. But, it is a bit tricky (and surprise too! ), you can access table in stored procedure only if you are EXPLICITELY granted such privileges.

I went to the server manager, and grant to myself select on all tables, my procedure worked just fine.

Hope this will help!

In article <4gi3bj$efr_at_sensible.teleord.co.uk>, marks_at_sensible.teleord.co.uk (Mark Styles) writes:
> Chris Xia <xia__chr_at_tasb.org> wrote:
> >>xia__chr_at_tasb.org (Chris Xia) wrote:
> >>Can I access tables that are not inside my schema when I create a
> >>stored procedure?
> >>According to the syntax of pl/sql , seems I should be able to do
> >>that , but I always got the error:
> >>
> >>4/33 PLS-00201: identifier 'DBA_OBJECTS' must be declared
> >>
> >I think I should supply a little bit more information here. I can do
> > select * from dba_objects;
> >just fine in sqlplus, and I have dba privilege.
>
> I've messed about with this, and its got me a bit stumped! I tried
> to create the following procedure:
>
> create or replace procedure fred as
> lv_fred varchar2(30);
> begin
> select owner into lv_fred from dba_catalog where rownum=1;
> end;
>
> And I got the same error (compiling as SYSTEM)
>
> However, when I tried:
>
> declare
> lv_fred varchar2(30);
> begin
> select owner into lv_fred from dba_catalog where rownum=1;
> end;
> /
>
> in SQL*Plus, it worked ok!
>
> I thought maybe it was because dba_objects is a synonym, so tried
> changing it to sys.dba_catalog, but still got the same errors.
>
> I then thought maybe it was because sys.dba_objects is a view, so
> created a view as another user, and tried to reference that, but
> this worked fine!
>
> The only conclusion I can come to is that the data dictionary tables
> are 'special' in some way, a conclusion I am not very happy with!
> Can anybody do any better?
>
> --
> ** Mark Styles aka Small -- Opinions expressed here are my own -- **
> ** marks_at_teleord.co.uk -- unless otherwise specified -- **
> ** Make Tea, Not Love **
Received on Thu Feb 22 1996 - 00:00:00 CET