Re: Weird happenings inside the package

Does the select still return the collect value if you precede it with the command

set role none;

and then do the select?

Martin

Peasant wrote:
>
> On Thu, 16 Aug 2001 11:05:19 +0200, I said:
>
> >I created package BOB as :
> >
> >create or replace package bob as
> > procedure getcount(v_tabname in varchar2);
> >end;
> >/
> >create or replace package body bob is
> >procedure getcount(v_tabname in varchar2) as
> > h_count number;
> > begin
> > select count(*) into h_count
> > from all_tables where table_name=UPPER(v_tabname);
> > dbms_output.put_line(h_count);
> > end
> >end bob;
> >/
> >create public synonym bob for bob;
> >grant execute on bob to public;
> >
> >If I execute the package using the login that created it (SYSTEM) then
> >it returns the correct value. If, however, I connect as another user
> >(that does have select rights on ALL_TABLES) the procedure returns
> >zero, but running the select statement that sets h_count returns the
> >correct value. Why ?
>
> More on this weirdness :
> 1. Using Oracle 8.1.7 on Redhat 6.2
>
> 2. User SYSTEM cannot 'see' tables owned by other users (execute
> bob.getcount('EMP')) returns zero, but SELECT COUNT(*) FROM ALL_TABLES
> WHERE TABLE_NAME=UPPER('EMP') returns 1 object (owned by scott).
>
> --
> Confused Peasant
Received on Sun Sep 02 2001 - 11:58:19 CDT