create table as_test as select * from dual

create or replace package as_pkg 
 is  
   procedure my_proc; 
end; 

create or replace package body as_pkg 
 is  
   procedure my_proc 
   is  
     nCount number; 
   begin 
     select count(*) into nCount from as_test; 
     dbms_output.put_line(nCount); 
   end my_proc; 
end; 

begin 
  as_pkg.my_proc; 
end; 

select  s.sql_id, s.sql_text, o.object_name, s.program_line# 
from v$sql s 
left join all_objects o 
  on s.program_id = o.object_id 
where s.sql_text not like '%v$sql%' 
and lower(s.sql_text) like 'select count(*) from as_test'