Re: a question for stored procedure

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                                    **