Integer-fieldtype instead of float (Oracle 12.1.0.1 -> 12.2.0.1)

[rdeutsch]

Hi

I use Delphi Tokio 10.2 and DOA 4.1.3.5.

In our app we use persistend fields which were "imported" from a Oracle-DB (12.1.0.1) as a floatfield.
Last week we migrate to Oracle Version 12.2.0.1 and now the open of the dataset crashes, because when i now import the same db-field its coming in as a integer fieldtype.

CREATE TABLE myTable
(lfnr NUMBER(10,0) NOT NULL,
id NUMBER(2,0) NOT NULL,
pz NUMBER(1,0) DEFAULT 0 NOT NULL)

The problem is the db-field "pz" with number(1,0).

Any idea what's happend here? Is there a TOraSession-property which can handle this behavior or is that a Oracle bug?

Thanks for your support and best regards,
Robert

 

[rdeutsch]

Hello

Meanwhile we found out that the problem depends on the sql-statement:

Try this sql:

select min(k.pz) min_pz
from myTable k
group by k.lfnr

Running against Oracle 12.1.0.1 results in a persistents fieldtype = float.

Running against Oracle 12.2.0.1 results in a persistents fieldtype = integer.

Any idea what's happend here? Is there a TOraSession-property which can handle this behavior or is that a Oracle bug?

Thanks for your support and best regards,
Robert

 

[Marco Kalter]

I haven't tested it yet, but this indicates that Oracle 12.1 reports the scale or precision of the min_pz field differently than 12.2. You can probably force it to a float field by adding a dummy calculation. For example:

select min(k.pz) + 0.0 as min_pz
from myTable k
group by k.lfnr

 

[rdeutsch]

Hi Marco

You can probably force it to a float field by adding a dummy calculation.

For example, when adding the nvl-command i get with both Oracleversions the same "persisten field"-result -> then its always a float.

select min(nvl(k.pz,0)) min_pz
from myTable k
group by k.lfnr

But i hope that will not be the final solution. There are a lot of sql-statements which i would have to test and control and perhaps to modify also. A lot of works paired with some risk...

br, Robert