ORACLE NUMBER(18, 0) field generates TFloatField instead of TLargeintField on Delphi side

[tcxbalage]

Lets say I have a table with a NUMBER(18, 0) field.
If I add all fields to a TUniQuery (in design time) this field generates TLargeintField on Delphi side, which is fine.
The problem is when I make a PIPELINED function using a TYPE with a NUMBER(18, 0) field that gonna create a TFloatField type instead of TLargeintField.

Here is the code I tested with:

Code: Select all

create table MY_TABLE (ID_FIELD NUMBER(18, 0));

insert into MY_TABLE values (42);

create type MY_OBJECT is object (ID_FIELD NUMBER(18, 0));

create type MY_OBJECT_TBL is table of MY_OBJECT;

create function SELECT_AS_OBJECT return MY_OBJECT_TBL pipelined as
begin
  for v_rec in (select * from MY_TABLE)
  loop
    pipe row(MY_OBJECT(v_rec.id_field));
  end loop;
end;
/

create package MY_PACKAGE is

  type MY_RECORD is record (ID_FIELD MY_TABLE.ID_FIELD%TYPE);

  type MY_RECORD_TBL is table of MY_RECORD;

  function SELECT_AS_RECORD return MY_RECORD_TBL pipelined;
  
end;
/

create or replace package body MY_PACKAGE is

  function SELECT_AS_RECORD return MY_RECORD_TBL pipelined as
  begin
    for v_rec in (select * from MY_TABLE)
    loop
      pipe row(MY_RECORD(v_rec.id_field));
    end loop;
  end;

end;
/

On Delphi side use 3 TUniQuery with the following SQLs:

Code: Select all

select * from MY_TABLE;
-- this genereates TLargeintField, as expected

select * from table(SELECT_AS_OBJECT);
select * from table(MY_PACKAGE.SELECT_AS_RECORD);
-- these two genereates TFloatField, which is incorrect

Refering to your documentation (https://www.devart.com/unidac/docs/data_types.htm) TLargeintField expected in all cases.

[2] - The Oracle provider maps the NUMBER data type with different precision and scale to certain Delphi types depending on the provider options in the following way:
if scale equals zero, provider checks values of the specific options to choose the correct Delphi type in the following order:
1.1 field precision is less or equal PrecisionSmallint (default is 4) - uses ftSmallint;
1.2 field precision is less or equal PrecisionInteger (default is 9) - uses ftInteger;
1.3 field precision is less or equal PrecisionLargeInt (default is 18) - uses ftLargeint;

[MaximG]

We have investigated the way your queries work.
After executing the first and third queries, Oracle returns the following field type 'ID_FIELD' : NUMBER(18.0). In this case, we correctly represent this field as type ftLargeInt.
In the second query (select * from table (SELECT_AS_OBJECT)), the server returns the NUMBER type, which can have a fractional part. So in this case, we represent this field as type ftFloat.
The only solution in such a situation can be using DataTypeMapping.

For example: UniQuery.DataTypeMap.AddFieldNameRule('ID_FIELD', ftLargeint);

[tcxbalage]

Thanks for the quick reply.

You said in the second case the server returns the NUMBER type, which can have a fractional part.
I have some doubt about that.
If you modify the stored proc and multiply the v_rec.id_field value by some fractioned number, the result still will be a whole number, without any fraction.

Code: Select all

create or replace function SELECT_AS_OBJECT return MY_OBJECT_TBL pipelined as
begin
  for v_rec in (select * from MY_TABLE)
  loop
    -- multiply by 1.111, to make sure there will be fractions
    pipe row(MY_OBJECT(v_rec.id_field * 1.111));
  end loop;
end;
/

The result is 47.
That would be weird if I declare a NUMBER(18, 0) field and it could have a fractional part.
Also you can try this:

Code: Select all

create table TEMP_DUMP as select ID_FIELD from table(SELECT_AS_OBJECT) where 1=2;

The newly created TEMP_DUMP table's ID_FIELD type will be NUMBER(18), no scaling.

No offense, but your datamapping solution is not a helpful answer, its a turnaround solution.
That would seriously slow down the development time if I have to map every NUMBER(18, 0) field in every TUniQuery, including the existing ones.

Anyhow, if the third option [select * from table(MY_PACKAGE.SELECT_AS_RECORD)] would work, I would be happy, but it does not.
It still generates TFloatField on my side, how is that possible?
I1m using Oracle 19.8, RAC, with UniDAC 8.4.3, direct access.
If you know what causes the different behaviour, please let me know.

[MaximG]

Kindly note that after the changes you made, the select * from table (SELECT_AS_OBJECT) query will return the value 47 without the fractional part, but when determining the type of the field returned
query, we rely not on the return value or the definition of a field in the database (NUMBER (18, 0)), but on the Length and Scale values that the server returns to us when determining the type
ID_FIELD fields. When the select * from table (SELECT_AS_OBJECT) query is executed, the server returns 39 for both Length and Scale parameters, which is why we represent this field as type ftFloat.
The ID_FIELD field when executing the Select * From TEMP_DUMP query in our environment will correctly represent this field as type ftLargeInt.

[tcxbalage]

Thanks for the info.

Please lets concentrate to the [select * from table(MY_PACKAGE.SELECT_AS_RECORD)] soultion.

As you said, its working fine on your environment, but still generates Float for me.
What could be the problem? Why the different behaviour?

My environment:
Oracle 19.8, RAC + UniDAC 8.4.3, direct access + Delphi 7 + Win10 64bit.

[Warrior1301]

Hi All,

is this problem solved?
I have a same issue.
I found that the retrieved scale is 0, so the devart delphi code change the size 39, which is decoded to TFloatField.
The problematic field is OQ0QO0COQ0 in object OOOOO0QOQ0.
When I checked with a working query, this value was 18.

Waiting for your answer
Attila

[MaximG]

Thank you for the information. Please send us a sample project where the issue can be reproduced, including the DDL script for creating database objects used in the project. For your convenience, please use the e-support form https://www.devart.com/company/contactform.html