Node-oracledb: fetchAsString decimal number notation

[cemremengu]

I am not sure if this a JavaScript feature so apologies for the noise in advance but fetchAsString for NUMBER type returns decimals that start with dots such as .55

Is there any way to make it return 0.55 ?

[anthony-tuininga]

This is making use of the conversion that Oracle does on the server. You can see the same results if you perform a query such as "select 1 / 2 from dual". Why do you want the leading zero? Javascript's parseFloat() certainly doesn't need it!

[anthony-tuininga]

We could make use of ODPI-C's conversion of numbers to text which does include the leading zero (it always normalises it that way), but not sure if that is desirable for everyone. :-)

[cemremengu]

Thanks for the quick response! I actually wanted it for presentation purposes since .55 can easily be read as 55 on UI side :) It would be great if there was an option for that since it may not be optimal to monkey patch this after I fetch the data.

[anthony-tuininga]

Well, you could simply fetch the results as numbers and then call toString() on the numbers -- as that does what you want. I'll see what others think about using the ODPI-C number conversion method instead of the Oracle server conversion method.

[cemremengu]

Thanks for the suggestion, I will use that as a workaround for now but it would be great to fetch that representation directly. I am not sure if it will be a breaking change though.

[cemremengu]

Well, you could simply fetch the results as numbers and then call toString() on the numbers -- as that does what you want. I'll see what others think about using the ODPI-C number conversion method instead of the Oracle server conversion method.

By the way, this does not work since I get the leading zeroes but then hit by the imprecision problem (i.e. 0.6900000000000001)

I would have to try parse float and then do toString which is really not desirable :/ but I guess that works for now.

[anthony-tuininga]

Yeah. There is a difference between how Oracle transforms floating point numbers to strings and how Python/Node.js transform floating point numbers to strings. So you'd have to do the whole "fetchAsString" thing, but then call parseFloat(value).toString() in order to get it into the format you want.

[danilohgds]

Thanks for the quick response! I actually wanted it for presentation purposes since .55 can easily be read as 55 on UI side :) It would be great if there was an option for that since it may not be optimal to monkey patch this after I fetch the data.

@cemremengu You can use bignumber.js package and force the string to your format. This can be done in frontend or backend, the decision is up to you. No reason for the leading zero from a JS perspective since everything mathematical related already handles such representation ✌️

[cjbj]

Closing this. I made a note to revisit using ODPI-C's number conversion.

[luobotang]

have the same problem, hope there will be an option to use format like "0.55".

[lkiii]

@luobotang and everyone else who might be experiencing this. The only option I was able to find around this is to manually check if the field type is the Number and fix the formatting issues.

I have used oracledb.extendedMetaData = true global option and then checked if metaData.dbTypeName === 'NUMBER' and if it does parseFloat(value).toString()

[cjbj]

@lkiii what are you doing with the value once you've formatted it? Passing it to something? Displaying it?

[lkiii]

The JSON response is being sent back to the front-end

[cjbj]

And why does the format matter? A serious question, for my education.

[lkiii]

Because front-end is busy with other tasks. Of course, they could just format image on the front-end and it would make the most sense.

[danilohgds]

@lkiii if you are going to use fetchAsString, it makes sense for you to instead let Oracle do the formatting to you right away, meaning use a to_char with a formatting mask to allow .55 to become 0.55

Similar to this:

select to_char(.55, '0.99') from dual;

not appealing to the eye, but it does help:
formats here: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Format-Models.html#GUID-096CA64F-1DA3-4C49-A18B-ECC7518EE56C