Question about Handling Numeric Data with ODPI-C

[denis-beurive]

Hello everyone,

I started working with ODPI-C today as part of my job, and I came across some advice that left me puzzled.

A colleague strongly recommended never converting numeric data from the database into native numeric types in my program. According to them, this is a "trap" because the database may internally use more bits to store a value than the corresponding C types can handle.

Here's my understanding of how things should work:

• If the native type of a value (fetched from the database) is DPI_NATIVE_TYPE_INT64, I would assume I should use an int64_t variable to store it.
• Similarly, for DPI_NATIVE_TYPE_UINT64, I’d use a uint64_t.
• For DPI_NATIVE_TYPE_FLOAT, I’d use a float.
• For DPI_NATIVE_TYPE_DOUBLE, I’d use a double.

Note: The native type can be determined using the function dpiStmt_getQueryValue.

My questions are:

• Are there known issues with these assumptions?
• Could there be situations where, for example, a value with a native type of DPI_NATIVE_TYPE_INT64 cannot safely fit into an int64_t variable in my program?

I understand this might seem like a basic question, but the people around me seem very convinced that I should be wary of trusting the native type entirely.

I’d greatly appreciate your insights!

Thanks in advance.

[anthony-tuininga]

It is indeed true that the Oracle Database can store numbers that are incapable of being stored accurately in int64_t, uint64_t, float or double. You can use the metadata, however, to determine whether or not it is safe for numbers to be returned as any of these types. Internally, ODPI-C does look at the metadata and will change from the default DPI_NATIVE_TYPE_DOUBLE to DPI_NATIVE_TYPE_INT64 if the metadata allows for returning those numbers safely as integers without loss of precision. That said, the developer can select any of the types, in which case the developer should know the contents of the data or be prepared for loss of precision. The use of DPI_NATIVE_TYPE_BYTES ensures that there is never any loss of precision. You can then convert to whatever is needed -- which is what the python-oracledb driver does, for example.

Note that the Oracle Database stores NUMBER values in packed decimal format. As such, using float/double will frequently result in loss of precision due to the simple fact that lossless conversion between binary and decimal formats is not possible in all cases. This may be the "trap" that you are being warned about! You can use BINARY_DOUBLE and BINARY_FLOAT if you want to use the C double/float types with a guaranteed seamless transfer between Oracle Database and your program -- or you can take this into account and adjust your application accordingly.

Hope this helps!

[denis-beurive]

Thank you very much for this insightful explanation. I truly appreciate the clarity and depth of your response!