[ACCEPTED]-Oracle 'printf' equivalent-printf

The closest standard approximation to printf 3 for Oracle I can think of is utl_lms.format_message. However, it 2 won't work in SQL statements, that is, this 1 is ok:

begin
  dbms_output.put_line(
    utl_lms.format_message('hello %s, the number is %d', 'world', 42)
  );
end;
/

but this gives a ORA-00902: invalid datatype error:

select utl_lms.format_message('hello %s, the number is %d', 'world', 42)
  from dual

No there are no built-in Oracle functions 8 that apply a formatting string in this fashion. Although 7 it would be easy to write a custom function 6 for this specific example, writing a PL/SQL-based 5 implementation of printf would be challenging.

If 4 you have a frequent need for this, perhaps 3 you could write an Oracle function that 2 wraps a Java call for a richer string handling 1 environment.

I've made a simple template engine named 6 ora_te (on GitHub) for Oracle SQL / PLSQL. With the help of 5 it your goal can be achieved in the following 4 ways:

Noneffective implementation with multiple 3 parsings of template string:

with acid_batch as (
  select rownum as mix_type, rownum + 2 as mix_num 
  from all_objects
  where rownum < 10
)
--
SELECT pk_te.substitute('$1 ($2)', ty_p( mix_type, mix_num ) ) as description
FROM acid_batch
WHERE mix_num < 10;

An effective 2 implementation with one time compilation 1 (parsing):

with acid_batch as (
  select rownum as mix_type, rownum + 2 as mix_num 
  from all_objects
  where rownum < 10
),
--
o as ( 
  select ty_te.compile_numbered( '$1 ($2)' ) te from dual
)
SELECT pk_te.substitute( o.te, ty_p( mix_type, mix_num ) ) as description
FROM acid_batch, o
WHERE mix_num < 10;

BTW it also supports named placeholders.

Just another idea for you: I've found REPLACE 9 to be useful for this kind of thing, especially 8 when the template is complex:

SELECT REPLACE(REPLACE(
        '%mix_type% (%mix_num%)' /*template*/
       ,'%mix_type%', mix_type)
       ,'%mix_num%' , mix_num ) as description,
FROM   acid_batch
WHERE  mix_num < 10

The only downside 7 is you need to add as many REPLACE('s as there are 6 variables to replace - but at least you 5 only need to have one per variable, regardless 4 of how many times it appears in the template.

(NOTE: There is no particular significance to using "%" as a delimiter, it's just a personal convention of mine - you might choose a different pattern, e.g. <mix_type> or [mix_type])

For 3 this particular instance it looks like overkill, but 2 in some cases it can make things much easier, e.g.:

template := 'bla bla %a% %b% %a%';
output := REPLACE(REPLACE(template
    ,'%a%', some_complex_expression)
    ,'%b%', b);

Compare 1 the above with:

output := 'bla bla ' || some_complex_expression || ' ' || b || ' ' || some_complex_expression;

You can resolve it in the select.

SELECT mix_type || '(' ||  mix_num || ')' as description,
FROM acid_batch
WHERE mix_num < 10

you should 3 also take a look at the functions

to_char

to_date

to_number

as they 2 give your a finer granularity on how you 1 want the things represented.