DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Changing separators on-the-fly in 9i (NLS_NUMERIC_CHARACTERS)?

-K-Jun 16 2010

Hi,

I'm looking for a way to personalize/localize output of our 9i reports.
Currently, the server is set to output in American format, which uses "dot" as decimal separator, which is generally fine.
However, we need to output "comma" as decimal separator when e.g. outputting to a Spanish Excel, else it won't recognise the number format.

As I know when the user wants one or the other (by parameter), best solution would be issuing something like

SRW.DO_SQL('ALTER SESSION SET NLS_NUMERIC_CHARACTERS='',.''');

DBMS_SESSION.SET_NLS('NLS_NUMERIC_CHARACTERS', ''',.''');

in the BEFORE REPORT trigger, but that doesn't seem to work.
Neither do the DECIMAL and THOUSANDS system parameters, which I have been told are obsolete.
So currently the only option I see is converting all numbers 1 by 1 with to_char, but that would take sooooo long converting all reports. Additionally, if another programmer adds a new report or new number to an existing one, he'd likely overlook the hack and we'd get mixed output.

Any suggestion is appreciated,
K.