Mark This Down

You may have heard me say it before, but I like writing documentation. That includes both help text within an application and the comments describing how to use APIs. I don’t, however, like writing documentation that never gets read. Or worse still, documentation that gets written once but is never updated as things change, so it’s wrong. And documentation that is redundant, I also have no time for that.

Given that, I recently decided to write all of my package specification (PKS) comments in a way that allows me to easily convert them to HTML in real time. The documentation is always up to date—taken directly from the PKS. It has a bonus that it makes it extremely easy to copy and paste example code from the documentation into a code editor.

Hat tip to the APEX development team. I noticed that the APEX documentation appears to match the APEX package specifications—except that the APEX package specifications appear to use Markdown syntax.

Below is my process.

Write your comments as Markdown

It’s really that easy, but defining a few standards will improve your results. An example will help. NOTE: In the example below I am using three standard ticks (‘) before and after example code. But in reality you should use three back ticks (`) or three tildas (~). If I use three back ticks, though, it messes up Hashnode’s formatting. So…just remember that those three ticks should be three back ticks.

--==============================================================================
-- ### Globals
--==============================================================================

-- **gc_excluded_schemas**
-- provides a list of schemas that cannot be searched
gc_excluded_schemas         apex_t_varchar2 := apex_t_varchar2( 'XDB', 'SYSAUX','CTXSYS','MDSYS','SYSTEM');

-- **gc_excluded_tables_views**
-- provides a list of tables and views that cannot be searched (regardless of schema)
-- ~~~sql
gc_excluded_tables_views    apex_t_varchar2 := apex_t_varchar2('XXIVS_SEARCH','XXIVS_SEARCH_APP', 'XXIVS_RESULT','XXIVS_RESULT_LINK', 'XXIVS_SEARCH_SESSION_V', 'XXIVS_RESULT_SESSION_V','XXIVS_RESULT_LINK_SESSION_V', 'XXIVS_LINK_V');
-- ~~~
-- **gc_excluded_columns**
-- provides a list of columns that cannot be searched (regardless of schema or table)
-- ~~~sql
gc_excluded_columns         apex_t_varchar2 := apex_t_varchar2('PASSWORD','PUBLIC_KEY','PRIVATE_KEY','P__1','P__2','P__3'); -- 'P__1','P__2','P__3' are reserved for use inside this pkg.
-- ~~~
-- **gc_search_data_types**
-- indicates the data types available to be searched
-- ~~~sql
gc_search_data_types        apex_t_varchar2 := apex_t_varchar2('VARCHAR2', 'CHAR','NVARCHAR2', 'CLOB', 'NCLOB', 'NUMBER'); -- ***TODO:  'BLOB'
-- ~~~
-- ### Statuses
-- ~~~sql
gc_initiated                varchar2(200) := 'Initiated';
gc_in_progress              varchar2(200) := 'In Progress';
gc_complete                 varchar2(200) := 'Complete';
gc_error                    varchar2(200) := 'Error';
gc_killed                   varchar2(200) := 'Killed';
-- ~~~
--==============================================================================
-- ### function excluded_schemas
--
-- This function returns the value of the constant gc_excluded_schemas so that it can be used in SQL.
-- 
-- example:
-- ~~~sql
--  select distinct owner d, owner r
--    from all_tables
--    where owner not in (select column_value from table (xxivs_vast_search.excluded_schemas) )
--    order by owner
-- ~~~
--==============================================================================
function excluded_schemas return apex_t_varchar2 deterministic;

I made several decisions on how to format my output. These decisions go hand in hand with the PL/SQL function that I will provide below. You may decide on different standards.

1. In my output I only keep lines that start with "-- " (that’s dash dash space) or “gc” (for global constant). The space at the end of "-- " allows me to control formatting. A comment without the space allows me to add a blank line in the PKS but not in the HTML output. I also like to provide the values of global constants as part of the HTML output and my standard is to prefix these constants gc_.

2. I “throw away” the leading "-- " of every line.

3. I use ## and ### to indicate sections, e.g. constant sections, procedure names, etc.

4. I use ** around some things (within the comments) to make them “strong.”

5. I use surround examples with … well … take a like at the code block above. It’s super hard to give the example here because Hashnode wants to use convert the example into a code block!

Use a tiny function to convert your comments to HTML

Below is the function I use:

create or replace function get_pks_comments_html(  
                            p_package_name      in varchar2,
                            p_package_owner     in varchar2 default sys_context('USERENV', 'CURRENT_SCHEMA')) return clob is
l_markdown_clob       clob;
l_line                varchar2(32000);
begin
    for line in 
        (select text
           from all_source
          where type = 'PACKAGE'
            and name = p_package_name
            and owner = p_package_owner
            and (text like '-- %' or length(text) = 0 or text like 'gc\_%' escape '\' )
           order by line
        ) loop

        l_line := regexp_replace(line.text, '^-- ', '');

        l_markdown_clob := l_markdown_clob || l_line;
    end loop;

    return apex_markdown.to_html(p_markdown => l_markdown_clob,
                                 p_softbreak => apex_application.LF,
                                 p_extra_link_attributes => apex_t_varchar2('target', '_blank'));

end get_pks_comments_html;
/

This code block implements the standards I mentioned above. It’s worth noting that the value of TEXT in the ALL_SOURCE table always appears to end with a CHR(10)—which is equivalent to apex_application.LF.

Add a Dynamic Content region to your APEX documentation page

In your APEX application you can simply add a region to your application that has the following code:

return get_pks_comments_html('XXIVS_VAST_SEARCH');

If you have more than one package, you can have a select list and use the following:

return xxivs_vast_search.get_pks_comments_html(:P10_PACKAGE_NAME);

The results!

This is how it turns out without any additional formatting.