Blitz Report Extension Package API Signatures

select
y.package_name,
y.procedure_name,
y.overload,
y.data_level,
y.position,
y.argument_name,
y.data_type,
y.type_name,
y.in_out,
y.defaulted,
y.referenced_by
from
(
select
ua.package_name,
ua.object_name procedure_name,
ua.overload,
ua.data_level,
ua.position,
ua.argument_name,
ua.data_type,
ua.type_owner||decode(ua.type_name,null,null,'.'||ua.type_name)||decode(ua.type_subname,null,null,'.'||ua.type_subname) type_name,
ua.in_out,
ua.defaulted,
x.referenced_by,
ua.sequence
from
(
select /*+ no_merge */
s.package_name,
s.object_name,
listagg(s.referencing_package,', ') within group (order by s.referencing_package) referenced_by
from
(
select distinct
upper(regexp_substr(us.text,'([a-z0-9_]+)\.([a-z0-9_]+)[[:space:]]*(\(|$)',1,rowgen.column_value,'i',1)) package_name,
upper(regexp_substr(us.text,'([a-z0-9_]+)\.([a-z0-9_]+)[[:space:]]*(\(|$)',1,rowgen.column_value,'i',2)) object_name,
us.name referencing_package
from
user_source us,
table(xxen_util.rowgen(regexp_count(us.text,'[a-z0-9_]+\.[a-z0-9_]+[[:space:]]*(\(|$)',1,'i'))) rowgen
where
:api_package_name is null and
us.type='PACKAGE BODY' and
us.name like 'XXEN%' and
us.name in (select us2.name from user_source us2 where us2.type='PACKAGE BODY' and us2.line=1 and us2.name like 'XXEN%' and lower(us2.text) not like '%wrapped%') and
regexp_like(us.text,'[a-z0-9_]+\.[a-z0-9_]+[[:space:]]*(\(|$)','i')
) s
where
s.package_name not like 'XXEN%'
group by
s.package_name,
s.object_name
) x,
user_arguments ua
where
x.package_name=ua.package_name and
x.object_name=ua.object_name
union all
select
ua.package_name,
ua.object_name,
ua.overload,
ua.data_level,
ua.position,
ua.argument_name,
ua.data_type,
ua.type_owner||decode(ua.type_name,null,null,'.'||ua.type_name)||decode(ua.type_subname,null,null,'.'||ua.type_subname),
ua.in_out,
ua.defaulted,
to_char(null),
ua.sequence
from
user_arguments ua
where
:api_package_name is not null and
3=3
) y
where
1=1
order by
y.package_name,
y.procedure_name,
nvl(y.overload,'1'),
y.sequence

select y.package_name, y.procedure_name, y.overload, y.data_level, y.position, y.argument_name, y.data_type, y.type_name, y.in_out, y.defaulted, y.referenced_by from ( select ua.package_name, ua.object_name procedure_name, ua.overload, ua.data_level, ua.position, ua.argument_name, ua.data_type, ua.type_owner||decode(ua.type_name,null,null,'.'||ua.type_name)||decode(ua.type_subname,null,null,'.'||ua.type_subname) type_name, ua.in_out, ua.defaulted, x.referenced_by, ua.sequence from ( select /*+ no_merge */ s.package_name, s.object_name, listagg(s.referencing_package,', ') within group (order by s.referencing_package) referenced_by from ( select distinct upper(regexp_substr(us.text,'([a-z0-9_]+)\.([a-z0-9_]+)[[:space:]]*(\(|$)',1,rowgen.column_value,'i',1)) package_name, upper(regexp_substr(us.text,'([a-z0-9_]+)\.([a-z0-9_]+)[[:space:]]*(\(|$)',1,rowgen.column_value,'i',2)) object_name, us.name referencing_package from user_source us, table(xxen_util.rowgen(regexp_count(us.text,'[a-z0-9_]+\.[a-z0-9_]+[[:space:]]*(\(|$)',1,'i'))) rowgen where :api_package_name is null and us.type='PACKAGE BODY' and us.name like 'XXEN%' and us.name in (select us2.name from user_source us2 where us2.type='PACKAGE BODY' and us2.line=1 and us2.name like 'XXEN%' and lower(us2.text) not like '%wrapped%') and regexp_like(us.text,'[a-z0-9_]+\.[a-z0-9_]+[[:space:]]*(\(|$)','i') ) s where s.package_name not like 'XXEN%' group by s.package_name, s.object_name ) x, user_arguments ua where x.package_name=ua.package_name and x.object_name=ua.object_name union all select ua.package_name, ua.object_name, ua.overload, ua.data_level, ua.position, ua.argument_name, ua.data_type, ua.type_owner||decode(ua.type_name,null,null,'.'||ua.type_name)||decode(ua.type_subname,null,null,'.'||ua.type_subname), ua.in_out, ua.defaulted, to_char(null), ua.sequence from user_arguments ua where :api_package_name is not null and 3=3 ) y where 1=1 order by y.package_name, y.procedure_name, nvl(y.overload,'1'), y.sequence