PL/SQL doesnt return rows Please help me debug this:

Working:

When I execute:

set serveroutput on

declare
    middle_name     varchar2(30);
begin 
    middle_name := '0000000231';

    for x in (
            select distinct tablespace_name
            from all_tables
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_tab_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_indexes
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_ind_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_lobs
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_lob_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            order by 1
        )
    loop
            dbms_output.put_line('nir2');
    end loop;
end;
/


I see:

nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2

PL/SQL procedure successfully completed.


But when I execute:

create or replace package "PKG_PROVISIONING" as
    procedure prc_create_tablespace(    
        v_datadir in varchar2
    );      
end PKG_PROVISIONING;
/


create or replace package body "PKG_PROVISIONING" as
    procedure prc_create_tablespace(v_datadir in varchar2)
    is
        middle_name     varchar2(30);
    begin
        dbms_output.put_line('nir');
        middle_name := '0000000231';
        for x in (
            select distinct tablespace_name
            from all_tables
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_tab_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_indexes
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_ind_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_lobs
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_lob_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            order by 1
        )
        loop
            dbms_output.put_line('nir2');
        end loop;
    end prc_create_tablespace;
end PKG_PROVISIONING;
/


I see:

exec PKG_PROVISIONING.prc_create_tablespace('test');
nir

PL/SQL procedure successfully completed.


Both are from the same user. this use has DBA privelage.
Many times, when the code is not correctly working iinside package/procedure and is ok in anonymous PL/SQL; it comes from the AUTHID clause restrictions: 

http://psoug.org/definition/AUTHID.htm