Simple Oracle C Sprintf or Java String.format

It is amazing what one finds inside an Oracle database!

When writing pl/sql code I often have need to print out formatted strings.

I have often wanted Oracle to have an equivalent to the C sprintf or Java format functions.

Well, just the other day I discovered:

   utl_lms.format_message(
      format IN VARCHAR2 CHARACTER SET ANY_CS,
      args   IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL)
   RETURN VARCHAR2 CHARACTER SET format%CHARSET;

See Format Message

You will note from the documentation that only %s and %d are supported for strings and numbers. What is not provided is any sort of formatting of those strings or numbers. For example in Java we can do this:

   // Format the number 10 left justified in a string of 4 digits
   String result = String.format("Number: %4d", 10);
   // ie "Number:   10"

However, Oracle’s string functions can make up for what is lacking in the format_message function:

 SET serveroutput ON SIZE unlimited
 BEGIN
   dbms_output.put_line(utl_lms.format_message('Number: %d', 10));
   dbms_output.put_line(utl_lms.format_message('Number: %s', LPAD(10,4)));
 END;
 /
 SHOW errors
 anonymous block completed
 NUMBER: 10
 NUMBER:   10
 No Errors.

Most useful.

Have fun!