Oracle NUMBER data type is formatted in an unexpected way

[quang-do-se]

H2 version: 2.1.214
URL: jdbc:h2:mem:testdb;MODE=Oracle;

SQL to reproduce:

CREATE TABLE car
(
    "car_number" NUMBER
);

INSERT INTO car ("car_number") values (10200);

SELECT * FROM car;

CREATE OR REPLACE VIEW car_aggregate AS
SELECT '' || "car_number"
FROM car;

SELECT *
FROM car_aggregate;

Description: In Oracle mode, NUMBER data type is parsed in a strange and unexpected way when it is converted to a string value. If a NUMBER value contains trailing zeros, it will be formated as a scientific notation. Please see screenshot below.

[katzyn]

When you use vendor-specific data types you should realize that they aren't expected to be supported by other database systems. H2 is not an emulator of Oracle and doesnt't have its special data types. NUMBER with parameters is converted to NUMERIC with the same parameters and NUMBER without parameters is converted to DECFLOAT(40) by H2, because these data types can represent these values better than any other data types.

You need to use some numeric data type with exact precision for this column. If it can have only integer values, you can use standard INTEGER, NUMERIC(10), or Oracle-specific NUMBER(10) in both Oracle and H2. For columns contaning values with fractional part you can use NUMERIC(20, 5) or Oracle-specific NUMBER(20, 5) etc.

[quang-do-se]

Thank you @katzyn. It makes sense. However, this issue didn't happen in H2 version 1. Our unit tests were suddenly failed when I updated to H2 version 2.1.214.

In my opinion, DECFLOAT should keep the original value instead of formatting it to scientific notation.

[katzyn]

Numeric data types preserve only the actual number. They don't preserve its textual representation.

10200 literal is the SQL Standard represents an value of an implementation defined exact numeric data type with scale 0, for example NUMERIC(5), or INTEGER, or whatever else.

Literals with mantissa like 1.02E+4 represent values of an implementation-defined approximate numeric data type or decimal floating-point type (DECFLOAT).

So it isn't surprising that conversion from DECFLOAT to CHARACTER VARYING produces a string with mantissa. The SQL Standard allows, but not requires this behavior; H2 is compliant with the standard, but other database systems may return textual representation of any other numeric literal with the same value, so you cannot rely on it.

[janar-rahumeel]

Hi, is it possible to change the H2 internal type still to NUMERIC(H2_MAX_PRECISION, 0) when the mode is Oracle? NUMBER in Oracle becomes NUMBER(38, 0) by default as far as I know. This H2 e-notation behavior is causing too much effort in large projects.

[katzyn]

No, currently it isn't possible.

NUMBER and NUMBER(38, 0) in the Oracle are very different from each other. NUMBER without parameters is an approximate precision data type, similar to standard DECFLOAT(40) data type. NUMBER with parameters is an exact precision data type similar to standard NUMERIC data type with the same parameters.

Try this in Oracle:

CREATE TABLE TEST(N1 NUMBER, N2 NUMBER(38, 0));
> ok
INSERT INTO TEST VALUES (3.123, 3.123);
> 1 row
INSERT INTO TEST VALUES (12345678901234567890.0123456789012345678901, 12345678901234567890.0123456789012345678901);
> 1 row
INSERT INTO TEST(N) VALUES (1E45 / 7);
> 1 row
INSERT INTO TEST(N2) VALUES (1E45 / 7);
> ORA-01438: value larger than specified precision allowed for this column

SELECT * FROM TEST;
> N1                                            N2
> --------------------------------------------- --------------------
>                                         3.123                    3
>     12345678901234567890.01234567890123456789 12345678901234567890
> 142857142857142857142857142857142857143000000                 NULL

You shouldn't use NUMBER without parameters for integer values or values with any other exact scale in Oracle or H2, because it is possible to insert value with any scale into NUMBER column, this data type only limits number of significant digits to 40.

[janar-rahumeel]

Thanks, fair enough. Yes indeed NUMBER without parameters in Oracle is sort of floating number.