How to format amount values for French locale-CodePudding

We are using a stored procedure in Oracle 12c to calculate and return some amounts. As we have clients in various countries, we received a requirement to display the amounts as per French Locale format based a a particular environment variable.

Here is the exact issue:

The regular amount format would be like 5,000.00

For this, we use select (TRIM(TO_CHAR('5000','999,999,999,999,999.99') )) from dual;

-->5,000.00

But for French locale, it should be returned like 5 000,00

Please suggest if Oracle provides any functions to support amounts based on Locales than using Replace over the above statement when the locale is French:

select replace(replace((TRIM(TO_CHAR('5000','999,999,999,999,999.99') )),',',' '),'.',',')  from dual;

--> 5 000,00

CodePudding user response：

you can set NLS_TERRITORY parameter at a session level:

alter session set nls_territory='FRANCE';

select to_char('5000', 'fm99G999D00') as french
  from dual;
Result:

FRENCH   
----------
5 000,00