with
   base as (
      select i.owner, 
             i.object_type, 
             i.object_name, 
             i.name as procedure_name, 
             i.line as start_line, 
             lead(i.line-1 respect nulls) over(order by i.line, i.col) as end_line,
             max(
                case 
                   when i.usage = 'DECLARATION' then 
                      1 
                   else 0 
                end
             ) over (partition by i.signature) as is_private             
        from dba_identifiers i
       where i.object_type = 'PACKAGE BODY'
         and i.type in ('FUNCITON', 'PROCEDURE')
         and i.usage in ('DECLARATION','DEFINITION')
   )
select owner, object_type, object_name, procedure_name, start_line, end_line
  from base
 where is_private = 0;