Substitution Strings

[martindsouza]

From @martindsouza on August 5, 2013 15:45

Thanks to John Scott for this idea.

Would like the ability to support substitution strings. For example: "Updated %num_rows% in table out of %total_rows%".

John and I discussed possible implementation options. For now here's what is purposed:

• Add a new parameter call p_do_substitutions default false
• If user sets p_do_substitutions to true then it will leverage the p_params option for substitution switching rather than logging paramers

Ex:

logger.append_param(l_params, 'p_empno', p_empno);
logger.log('Employee %P_EMPNO%', p_params => l_params, p_do_substitutions => true)

Alternatively, instead of p_do_substitutions we can us p_param_type with 'DEFAULT'/'PARAMS' and 'SUBSTITUTION' (or something like that). We can create global constants to manage. This may be the better option.

Copied from original issue: tmuth#32

[martindsouza]

This is still very much up for debate as to how to implement this. Several options:

1. Recommend developers to just use sys.utl_lms.format_message (see http://vbegun.blogspot.ca/2005/10/simple-plsql-printf.html)
   • Pro: Build right into Oracle and no work from Logger
   • Con: Only supported 10g and up
2. Build a wrapper to handle functionality
   • Wrapper would use sys.utl_lms.format_message if available

Decision: Built a wrapper to support option 2. This just takes in a set of strings for the substitution strings.

Example:

declare
  l_ename varchar2(255) := 'bob';
  l_sal number := 3;
  l_scope logger_logs.scope%type := 'test';
begin
  logger.log(logger.get_fmt_msg('My name is %s. My sal is %d. Unreferenced: %s', l_ename, l_sal), l_scope);
end;
/

My name is bob. My sal is 3. Unreferenced:

Still need to decide how to implement this:

1. Use function (like above) and all other logger calls behave as is.
2. Create a new procedure called printf (or similar) that looks like: logger.printf(p_msg, p_s01, p_s02..., p_scope, p_level)
   • User would need to explicitly defined scope and level each time. This could slow down development time.
3. Write wrapper functions for printf. ex: logger.log_printf(p_msg, p_scope, p_s01 ...)
   • Again, names are up for debate

My preference is for option 1 as it keeps things consistent with logger. If we keep option 1 need to determine name.

1. get_fmt_msg
2. f
3. getf
4. printf

Though printf is more standard, it is not a true "print" procedure as it's not printing, rather returning a value.

[martindsouza]

From @rimblas on June 16, 2014 14:20

I like the printf idea and sys.utl_lms with a wrapper sounds like the perfect way to go. My suggestion, add "f" or "printf" as a function that log can use.
logger.log(logger.f(p_msg, p_arg1, p_arg2...), p_scope);

Alternatively, there would be value to a "logf" that merges log and printf into one call. In this case I would just have scope as the first parameter and forgo the others and follow with msg and args. Perhaps even skip the scope and set it manually in a separate call as you had mentioned. That seems like a useful approach too.

[martindsouza]

From @JuergenSchuster on June 17, 2014 6:46

I'm not a 100% sure if I understood Jorge's approach, but if it is working the way I think it is, I like the idea, and the printf :-)

[martindsouza]

From @goodwish on December 31, 2014 2:34

When will 2.2.0 release?

Thanks,
Charlie 木匠 | DBA

[martindsouza]

From @goodwish on February 19, 2015 15:44

Howdy,

When will we get this feature ? Is there an estimate ?

Or, can I contribute on it?

Thanks,
Charlie 木匠 | Database Architect Developer

[martindsouza]

Still up in the air about name for this. After reading http://en.wikipedia.org/wiki/Printf_format_string here are a list of names that I like

sprintf: Used in PHP, Pyhton, C++.
format: Used in Java as part of String object
format_message: This is what sys.utl_lms uses.

Names I no longer want to use:

printf: We're not printing using dbms_output.put_line.
get_fmt_msg: I think this is a bit long and not standard with any other language.

Note the API call will look something like this:

logger.log(logger.FN_NAME('My name is %s. My sal is %d', l_ename, l_sal));

[rimblas]

I'm still "happy" with printf, still consistent with C, but if it's off the table, sprintf would be perfect. sprintf best reflects that you're applying values to a string.
Couldn't you just recommend people use utl_lms.format_message and be done with it? 😄

[martindsouza]

@rimblas Thanks for your feedback. Yes we could use utl_lms.format_message however it does not support the explicit replacement of %s<n> replacements. For example:

sys.utl_lms.format_message('%s %s %s1', 'one', 'two', 'three'); returns one two three1. Ideally it should replace %s1 with the first parameter. I.e. I would like it to return one two one.

Regarding naming, if we run into future naming issues with sprintf we can always add additional wrapper functions to support multiple names.

[goodwish]

printf is good.