Typemismatch: expected Integer found Float (Oracle 12)

[Schlueter]

I think this topic is related to
Typemismatch: expected Integer found Float (Oracle 11)
but also very strange.

Using Delphi XE8, DOA 4.1.3.5, DB 12.1.0.2.0, Client 11.2.0.1.0

I've created a simple table like this:

create table TESTTABLE
(
  test1 NUMBER(1),
  test2 NUMBER(1),
  test3 NUMBER(1)
);

And I've filled the table with some data:

INSERT INTO Testtable VALUES(1, 1, 1);
INSERT INTO Testtable VALUES(1, 1, 2);
INSERT INTO Testtable VALUES(1, 1, 3);
INSERT INTO Testtable VALUES(1, 2, 1);
INSERT INTO Testtable VALUES(1, 2, 2);
INSERT INTO Testtable VALUES(1, 2, 3);
COMMIT;

I've used a TOracleDataSet with this query:

SELECT test1, test2, COUNT(*) As Quantity
  FROM Testtable
 WHERE test1 = 1
 GROUP BY test1, test2

With my old Oracle DB 10.2 the field 'test1' was for DOA of integer type, but now with Oracle DB 12.1 the field becomes of float type.
If I remove the WHERE clause or the GROUP BY (without COUNT), 'test1' will be recognized as an integer.
Do you know a reason for that behaviour?

Then I've put my query into a view (V_TEST) and run another query:

SELECT Data_Precision
  FROM User_Tab_Columns
 WHERE Table_Name = 'V_TEST'
   AND Column_Name = 'TEST1'

The result is 1.

But if I run the query as described in the related topic by coding:

begin
  TestQuery.Execute;
  s := 'Precision = ' + IntToStr(TestQuery.FieldPrecision(0));
  s := s + ', Scale = ' + IntToStr(TestQuery.FieldScale(0));
  ShowMessage(s);
end;

The result for precision and scale is 0.

I know the workaround by changing the queries so that the fields will also become of type float in DB 10.2, but it's a lot of work. Perhaps you know a more simple way?

Best regards
Rolf

 

[Marco Kalter]

Unfortunately there is no simpler way to fix this than modifying the queries.

 

[Ruudbern]

Still no other way to tackle this?
We have a database with over 1000 of type number(1) fields.
It will take weeks for us to check all queries in our application.

 

[Marco Kalter]

I'm not aware of any other option. But it may be possible to do an automated search of your .pas and .dfm files if you have a list of all number(1) columns.

 

[Ruudbern]

Hi Marco,

Thanks for the super fast reply.

It seems that multiplying the field by 1 in the where clause does the trick.
In the given example test1 changes to integer again:

SELECT test1, test2, COUNT(*) As Quantity
FROM Testtable
WHERE test1*1 = 1
GROUP BY test1, test2

i will try to do the automated search you suggested.