Typemismatch: expected Integer found Float (Oracle 11)

[lecare039]

This problem somehow seems to be related to http://www.allroundautomations.com/ubb/ultimatebb.php?ubb=get_topic;f=1;t=002569

After adding a row via

alter table T add (C number(1) default 0 not null);

and then trying to get this as integer, I'll always get a float back. This problem does only occur running a 11g db! Using 9i or 10g it works as expected.

By locating the problem, I also tried

alter table T add (C number(1) default 0);

. Using this sql, I'll get an integer - as expected.

Also this problem seems to occur only, while adding a row to an already created and filled/ used table. While creating the table with "not NULL"-rows, I'll get an Integer.

But as you can imagine the "not Null"-option is essential for me.

So why is a here a float returned? Is it a bug in Oracle 11g or in the DOA - I think it's a bug.

Thanks in advance for any responds.

edit: Using newest DOA and Turbo Delphi 2006.

 

[Marco Kalter]

From your description it seems like an 11g bug. Direct Oracle Access derives the data type from the scale and precision as reported by the Oracle Server.

We'll check it out to see if we can reproduce this.

 

[lecare039]

Checking USER_TAB_COLUMNS the data_type, -precision and -scale, looks right.

TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
DATA_TYPE
--------------------------------------------------------------------------------

DATA_PRECISION DATA_SCALE N
-------------- ---------- -
VERTRAG_AKTEN KNDREGEL
NUMBER
1 0 N

 

[Marco Kalter]

Yes, but the question is what precision & scale the Oracle Client reports when this table is queried...

 

[lecare039]

Well, creating a small test-project with Ora-Session and Ora-Dataset, adding the field of interest to us, it is set to TFloatField. The ObjectInspector shows a Pricision of 15 and OracleDataSet1KNDREGEL.DataSize returns 8. What does this value indicates? What scale the OC returns I don't know how to find out.

Hope I could help you and am waiting for a possible solution.

 

[Marco Kalter]

Can you try the following? Create a TOracleQuery instance with the following SQL:

select from

where

is the name of the table with the number(1) column, and is the column name. Next, run the following code:

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

Let me know what it says.

 

[lecare039]

Well, the result is this: 'Precision = 0, Scale = 0'

Strange, shouldn't precision be 1?

 

[lecare039]

So, made some more test:

alter table vertrag_akten add (TestRow1 number(1) default 0 not null);
alter table vertrag_akten add (TestRow3 number(1) default 1 not null);
alter table vertrag_akten add (TestRow2 number(1) default 0);
alter table vertrag_akten add (TestRow4 number(1) default 1);
alter table vertrag_akten add (TestRow5 number(2) default 0 not null);
alter table vertrag_akten add (TestRow6 number(2) default 0);

Results are:
'Precision = 0, Scale = 0'
'Precision = 0, Scale = 0'
'Precision = 1, Scale = 0'
'Precision = 1, Scale = 0'
'Precision = 0, Scale = 0'
'Precision = 2, Scale = 0'

So, as you can see, the problem is really the 'not null' statement.

 

[Marco Kalter]

Precision should indeed be 1. It seems like an Oracle bug.

 

[lecare039]

Is there any workaround other than not setting the field to not null? Because that wouldn't be the optimum.

 

[Marco Kalter]

You can force the field to a float field, regardless of database version, by using a dummy expression in your SQL. An example is posted here .

 

[lecare039]

Well, I need it Integer.

What I did now, is to create my own check constraint on adding the relevant row. But I would prefer to let it oracle do...

 

[lecare039]

Well, after searching Bugs on Metalinks ... My bug is comparable to an Bug that was reported concerning Date-fields on 11.1.

The solution is to first add the field with only defining the default-value and afterwards modify this field to set it to not NULL.

alter table testtable add testfield number(1) default 1;
alter table testtable modify filetype not NULL;

So it work'S as aspected and you don't need an explicit Constraint.

mfg