LoggerUtil: Create a Custom Template

Since I have written about my pet project about a month ago, I have made some major changes to the functionality of it.
If you haven't read that blog about my pet project, here's the synopsis:

I love Logger to instrument my code, I just don't like to type in all the bits and pieces to register all the input arguments when I write a new procedure or function. To solve this problem I have written a generator which takes the (packaged) procedure name and generates the body with all the instrumentation code in place. The only thing left to do is focus on the functionality that needs to be implemented in the first place.

First of all the name of the package changed. Now it is called LoggerUtil, which I believe is more inline with the functionality.
But that is only a minor change. The big change is that it is now possible to create your own templates for Procedures and Functions.

The basic mechanism to generate a template is the same as before, see my previous blog for an example or refer to the README.md file on the project page.

In order to use custom templates, you will need to use Logger release 3.0.1, because the supportive procedure to set and get custom preferences. Unfortunately it is not possible to use conditional compilation checking the version of Logger that you have installed.
To create a custom template you can use the procedure called "set_custom_template". This procedure takes two arguments:

1. P_TYPE: which kind of template do you want to store; a (F)unction or (P)rocedure.
2. P_TEMPLATE: a string containing your custom template

For example:

loggerutil.set_custom_template (p_type     => 'P'
                               ,p_template => 'your_custom_template'
                               );
   

The custom template is stored in the standard LOGGER_PREFS table with the custom preferences:

• CUST_FUNCTION_TEMPLATE
• CUST_PROCEDURE_TEMPLATE

Because of the current limitations of the LOGGER_PREFS table, your custom template cannot be longer than 255 characters.
There are some placeholders that you can use your custom template:
#procname#
The name of the procedure or function.
#docarguments#
All the arguments are listed (IN, OUT and IN/OUT). Handy for when you want to use this in the comments section. The text (or spaces) before the placeholder is placed before each argument.
#logarguments#
Only the IN and IN/OUT arguments are used for calls to Logger.

When you want to reset the custom templates and go back to the original use:

loggerutil.reset_default_templates;
   

You can find the LoggerUtil project on Github.

Splitting a comma delimited string the RegExp way, Part Three

The article read most often on this blog is called "Splitting a Comma Delimited String, the RegExp way".
On this blog there are two articles about this technique, one is about splitting up a single string into multiple rows and the other is about multiple strings into multiple rows.
Links to both articles are included at the bottom of this article.
It seems like there is a need for functionality like that frequently. And just to add to those two articles on the subject, here is a third one combining the first two articles.

Recently I was asked for help in a comment on how to go about and split up a string like the following

      'ABC/FDF,RET/YRT,UYT/ERT'
   

The expected outcome would be

      ABC
      FDF
      RET
      YRT
      UYT
      ERT
   

As you can see the input string consists of two different delimiters, namely a comma and a forward slash (/).
To split this string up, you will need both techniques from the other articles.

Let's start with a variable containing the input string.

      SQL> var input varchar2(150)
      SQL> 
      SQL> exec :input := 'ABC/FDF,RET/YRT,UYT/ERT'

      PL/SQL procedure successfully completed.
   

The first step is to split the string up using the first method, split up the string using the comma as a delimiter.

SQL> select regexp_substr (:input, '[^,]+',1, rownum) str
  2    from dual
  3   connect by level <= regexp_count (:input, '[^,]+')
  4  ;

STR
-------
ABC/FDF
RET/YRT
UYT/ERT
   

This will leave us with three records each consisting of a string that needs further splitting up, but this time with the forward slash as the delimiter.

Using these rows as the input in the next phase, use the technique described in the second article.
By introducing Subquery Factoring (lines 1-5), create a named query "commas"

   SQL> with commas
     2  as
     3  (select regexp_substr (:input, '[^,]+',1, rownum) str
     4    from dual
     5   connect by level <= regexp_count (:input, '[^,]+'))
     6   select regexp_substr (str, '[^\/]+', 1, rn) split
     7   from commas
     8   cross
     9   join (select rownum rn
    10       from (select max (regexp_count(rtrim (str, '/')||'/', '\/')) mx
    11        from commas
    12     )
    13    connect by level <= mx
    14    )
    15   where regexp_substr (str, '[^\/]+', 1, rn) is not null
    16   ;

   SPLIT
   ----------------------------------
   ABC
   FDF
   RET
   YRT
   UYT
   ERT

The forward slash has special meaning with regular expressions it needs to be escaped using a backslash.
You can see this on lines 6, 10, and 15.
What is interesting, or at least I find interesting, is the use of the RTRIM on line 10.
Each value per line is not completely delimited by the forward slashes, the trailing one is missing. Just to concatenate one to each line would be to easy, what if there is a trailing slash?
The RTRIM removes the trailing slash and concatenates one at the end, making sure that the string is split up at the right place.

Links

• Part One
• Part Two