When the app passes floating-point numbers via (hidden) APEX Items to and from javascript functions or to web-services, the decimal and grouping characters are constants!
These constants have to be passed to the function to_char and to_number as the second parameter (nlsparam).
That can be problematic,
1. because the precision and scale have to be fixed when formats with the grouping characters are needed. Since there is no combination of the FM9 mask with the G, I had to find a more flexible solution for number conversions.
2. javascript floating-point numbers can be surprisingly long before or after the decimal point.
I found a simple method that forms a fitting format mask on the fly.
The TRANSLATE function call maps the digits and signs to 9 and removes blank and currency characters. The REGEXP_REPLACE function call detects the exponent part and prepares the format string. I explored and expanded the method into the package numbers_utl.
https://livesql.oracle.com/apex/livesql/file/content_JTVYCKDJQF8MA8ZB5MM48XVC5.html
Link to the source file:
