How to use to_number function in Oracle for non US locales?-CodePudding

I have the number 123.45, but in portuguese, which is actually the locale that Oracle is currently using in our environment, this number would be 123,45, so when I try to run

SELECT TO_NUMBER('123.45') FROM DUAL;

I get ORA-06512 error.

Replace dot for comma is not an option because besides seeming a hacky solution, the oracle driver considers my java application locale and pass it on to the Oracle database session.

Is there anyway to consider both dots and commas as decimal separator in this function?

CodePudding user response：

You can try REFEXP, REPLACE Function as per your version of Database. There concept is repalce . with , and hope this will help.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm

https://www.techonthenet.com/oracle/functions/regexp_replace.php

CodePudding user response：

Use the D format model, passed as the second argument of TO_NUMBER, to represent the sessions current decimal character:

Which, if you were in America/England/etc. then you can use:

SELECT TO_NUMBER('123.45', '999D99') FROM DUAL;

And in Portugal/Germany/etc.

SELECT TO_NUMBER('123,45', '999D99') FROM DUAL;

If you want to always use . then explicitly use . in the format model:

SELECT TO_NUMBER('123.45', '999.99') FROM DUAL;

db<>fiddle here