SPRINTF functionality in PL/SQL?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: SPRINTF functionality in PL/SQL?

  1. #1
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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'

    Thanks,

    - Chris

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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 :).

    Thanks,

    - Chris


  4. #4
    Join Date
    Mar 2001
    Posts
    3
    You can also write an external function and load it into pl/sql (through extproc).

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Excelent point!

    Its true -usually we can't see the obvious...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width