Re: Weird happenings inside the package

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