Does anyone know of any Oracle-supplied PL/SQL procedure (in its *many* available scripts and add-ons) that can perform a basic sprintf?
For those that don't know, the sprintf function would allow the following:
sprintf (ResultingString, "Error: %s (%d) on line %d", l_ErrorText, l_ErrorID, l_LineNumber)
ResultingString might then be:
"Error: Bad Employee ID (-20010) on line 5"
I'd like to know if the functionality exists before 'rolling my own'
I don't think you'll find such functionalyty in any of the supplied pacakages.
There is very high probability you could find such a procedure (or at least something very simmilar) somewhere on the net, but I think you'll spend much less time writing your own then surfing for one ;).
The only thing that bothers me here is that sprintf obviously (I'm not familiar with this procedure, but I'm guessing it's comming from C - am I right?) takes any number and type of parameters. In PL/SQL you'll have to code the function with *fixed* number of parameters of *fixed* types.
The types should not be of too much troubles if you code them all to be VARCHAR2 - default conversion of numbers will be quite acceptable (I think), with dates you'll have to be sattisfied with the default conversion format. You could also try to complicate things and write an overloading packaged procedure for different types of different combination of parameters, but I don't think it is worth the troubles.
However the numbers of parameters will have to be fixed, so I guess you'll have to choose a sufficiently high number of parameters and default them all to NULLs
I was afraid you'd say that :).
You are correct. It originated in C and does take a variable parameter list, although I knew I was outta luck with that capability :).
You can also write an external function and load it into pl/sql (through extproc).
Its true -usually we can't see the obvious...