SQL & PL/SQL

Hello folks, I've landed into a strange problem and am not sure what's wrong.
All am trying to do is have a schema/user that is responsible to create public synonyms for the given owner user name. The code that I have mentioned below resided in a user called "buildman". This user has DBA role attached. So user can query all_objects for every owner to find the objects. But problem at high level is, the following query returns different results when run through a package versus run on SQL prompt.

select * from all_objects where owner = <the parameter> ;

Here is my code:

I have a type defined as below which is used in a package.

drop type t_synonym_rec_tab;
drop type t_synonym_rec;

create or replace type t_synonym_rec as object
( synonym_name varchar2(30)
 ,object_owner varchar2(30)
 ,object_name varchar2(30)
);
/

create or replace type t_synonym_rec_tab is table of t_synonym_rec;
/

I have a package code as below:

create or replace package build_util
as
  procedure create_synonym(p_synonym_tab t_synonym_rec_tab);
  procedure add_synonyms(p_owner_schema_name in varchar2);
  function get_syn_rec(p_owner_schema_name in varchar2) return t_synonym_rec_tab;
  function get_user_obj(p_owner_schema_name in varchar2) return int;
end build_util;
/

create or replace package body build_util
as

  procedure create_synonym(p_synonym_tab t_synonym_rec_tab)
  is
  begin
    if p_synonym_tab.exists(1)
    then
      for i in p_synonym_tab.first .. p_synonym_tab.last
      loop
        dbms_output.put_line('create public synonym '||p_synonym_tab(i).synonym_name||' for "'||p_synonym_tab(i).object_owner||'".'||p_synonym_tab(i).object_name);
--        execute immediate 'create public synonym '||p_synonym_tab(i).synonym_name||' for '||p_synonym_tab(i).object_owner||'.'||p_synonym_tab(i).object_name;
--        insert into temp values('create public synonym '||p_synonym_tab(i).synonym_name||' for "'||p_synonym_tab(i).object_owner||'".'||p_synonym_tab(i).object_name);
      end loop;
    else
      dbms_output.put_line('No synonyms to be created....');
    end if;
  end create_synonym;

  procedure add_synonyms(p_owner_schema_name in varchar2)
  is
    l_synonym_tab t_synonym_rec_tab;
  begin
    select t_synonym_rec(synonym_name, object_owner, object_name)
      bulk collect into l_synonym_tab
      from (select object_name as synonym_name, owner as object_owner, object_name
              from all_objects
              where owner = p_owner_schema_name
              and object_type in('TABLE', 'VIEW', 'TYPE', 'PROCEDURE', 'FUNCTION', 'PACKAGE') );

    dbms_output.put_line('Rows available in collection p_synonym_tab are '||l_synonym_tab.count);
    create_synonym(l_synonym_tab);
  end add_synonyms;

  function get_syn_rec(p_owner_schema_name in varchar2) return t_synonym_rec_tab
  is
    l_synonym_tab t_synonym_rec_tab;
  begin
    select t_synonym_rec(synonym_name, object_owner, object_name)
      bulk collect into l_synonym_tab
      from (select object_name as synonym_name, owner as object_owner, object_name
              from all_objects
              where owner = p_owner_schema_name
              and object_type in('TABLE', 'VIEW', 'TYPE', 'PROCEDURE', 'FUNCTION', 'PACKAGE') );
    return(l_synonym_tab);
  end get_syn_rec;

  function get_user_obj(p_owner_schema_name in varchar2) return int
  is
    l_cnt int;
  begin
    select count(*)
      into l_cnt
      from all_objects
      where owner = p_owner_schema_name
      and object_type in('TABLE', 'VIEW', 'TYPE', 'PROCEDURE', 'FUNCTION', 'PACKAGE');
    return(l_cnt);
  end get_user_obj;

end build_util;
/

Now if I log on to SQL prompt and do the below, I see different results.

SQL> sho user
USER is "BUILDMAN"
SQL> select count(*)
  2    from ALL_OBJECTS
  3    where owner = 'SYSTEM'
  4    and object_type in('TABLE', 'VIEW', 'TYPE', 'PROCEDURE', 'FUNCTION', 'PACKAGE');

  COUNT(*)                                                                      
----------                                                                      
       181                                                                      

SQL> select build_util.get_user_obj('SYSTEM') from dual;

BUILD_UTIL.GET_USER_OBJ('SYSTEM')                                               
---------------------------------                                               
                                8                                               

Edited by: girishkale on Jun 30, 2011 6:36 PM