PSQL to generate package overview

Wiki (en)
,
1

The following script was used to generate the related package documentation from the Data Dictionary of Invantive:

create or replace table packages@inmemorystorage
as
select pkg.package_name
       || '.A' 
       grouping_order
,      pkg.package_name
,      chr(13) || chr(10)
       || chr(13) || chr(10)
       || '## Generic package '
       || chr(96)
       || pkg.package_name
       || chr(96)
       || ': '
       || pkg.package_description
       || chr(13) || chr(10)
       || pkg.package_documentation
       || ' The following procedures and functions are available.'
       || chr(13) || chr(10)
       txt
from   SYSTEMPACKAGES@DataDictionary pkg
where  pkg.provider_name is null

create or replace table packagefunctions@inmemorystorage
as
select pkg.package_name
       || '.B.' 
       || pkn.function_name
       || '.A' 
       grouping_order
,      pkg.package_name
,      pkn.function_name
,      chr(13) || chr(10)
       || chr(13) || chr(10)
       || '### '
       || chr(96)
       || pkg.package_name
       || '.'
       || pkn.function_name
       || chr(96)
       || ': '
       || pkn.function_description
       || chr(13) || chr(10)
       || pkn.function_documentation
       txt
from   packages@inmemorystorage pkg
join   SYSTEMPACKAGEFUNCTIONS@DataDictionary pkn
on     pkn.package_name = pkg.package_name

create or replace table packagefunctionarguments@inmemorystorage
as
select package_name
       || '.B.' 
       || function_name
       || '.B'
       grouping_order
,      package_name
,      function_name
,      case
       when txt is not null
       then chr(13) || chr(10)
            || 'Parameters: ' 
            || chr(13) || chr(10)
            || chr(13) || chr(10) || '- '
            || txt
       else null
       end
       txt
from   ( select pfn.package_name
         ,      pfn.function_name
         ,      listagg
                ( argument_name 
                  || ': ' 
                  || argument_data_type
                  || ', '
                  || case
                     when argument_required
                     then 'not null'
                     else 'null'
                     end
                  || case
                     when argument_default_value is not null
                     then ', default ' || argument_default_value
                     else null
                     end
                  || case
                     when argument_documentation is not null
                     then '. ' || argument_documentation
                     else null
                     end
                , chr(13) || chr(10) || '- '
                )
                txt
         from   packagefunctions@inmemorystorage pfn
         join   SYSTEMPACKAGEFUNCTIONARGUMENTS@DataDictionary pft
         on     pft.package_name  = pfn.package_name
         and    pft.function_name = pfn.function_name
         group
         by     pfn.package_name
         ,      pfn.function_name
       )

declare
  l_txt varchar2;
begin
  for r
  in
  ( select grouping_order
    ,      pkg.txt
    from   packages@inmemorystorage pkg
    union all
    select grouping_order
    ,      pfn.txt
    from   packagefunctions@inmemorystorage pfn
    union all
    select grouping_order
    ,      pft.txt
    from   packagefunctionarguments@inmemorystorage pft
    order
    by     grouping_order
  )
  loop
    l_txt := l_txt || r.txt;
  end loop;
  dbms_output.put_line(l_txt);
end;