using Oracle 10g im accessing a table with an 'id' column of datatype NUMBER, data length 22, data precision 10 and data scale 0. The delphi app had previously been connected to a sybase database and cloumn 'id' was defined as an integer, so the column type for 'id' had been set to TIntegerField in the Delphi app. Now that im rebuiling the app to use the oracle database, i get an error telling me column 'id' is of type TIntegerField but should be type TFloatField. Surely the number column as previously defined equates to an integer. Is this a problem with DOA not being able to interpret the column type correctly ?

An integer field can be used for database fields upto a precision of 9 digits (2^31). For 10 digits the database can contain values that cannot be represented as a 32 bit integer.

However, if you are sure that the number(10) columns in the database will never contain values that are larger than a 32 bit integer, then you can set TOracleSession.Preferences.IntegerPrecision to 10 to get the desired behavior.

Same issue here. I understand the the problem. What I don't understand though, is the fact that this behaviour is depending on the oracle-version. With 9.0 I'm having no problems, from 9.2.0.6 up the above mentioned behaviour begins.
What is the trouble with filling a float with an integer?

There is no problem filling a float with an integer value, but unfortunately the standard TFieldDef class is rather strict when it comes to data types of persistent fields.

If differences occur between Oracle versions, then this is most likely a query with calculated fields such as aggregates (sum, avg, ...) or set operations (union, minus, ...). The differences occur for the scale or precision reported by Oracle, which in turn may result in a float or integer field in Delphi.

I am referring to this following issue (taken from the readme for patch 9.2.0.4):

[quote] 11.8 Event 10499

Event 10499 was introduced in release 9.2.0.3 to allow customers to revert to the earlier behavior of NUMBER datatypes that have no explicit setting of their scale value. Any non-zero setting of the event level will enable the old behavior.

Prior to the introduction of this event, server code could return zero for the scale if a NUMBER datatype was created without specifying the scale. Other code paths would return -127. With this event, all code paths return -127, as this is the correct behavior.

Client applications may rely on the previous (incorrect) behavior. Typically, client applications will test for a scale of -127 to distinguish between NUMBER and FLOAT datatypes. It is not sufficient to test the scale alone to distinguish between these two datatypes, one needs to test for a scale of -127 and a non-zero precision to identify a FLOAT.

Client applications that rely on the old behavior can still work as before if the event is set but will need to be corrected prior to upgrading to the next major release of the Oracle database.
[/quote]So now I have found a way to get the application back running, but what is bothering me is the last sentence... Will allroundautomations consider this, or must I change my applications?

Thanks
Sherlock

The current 4.0.6 already works as described above.

This is a serious problem for us, and it is not solved in the 4.0.7.1 version of DOA. I also tried this using both Delphi 6 and Delphi 2005, and this makes no difference.

The situation we have is the following: we have a number of queries using UNION, or based on views which in turn also use UNION. The application has been built and tested on Oracle 9. When testing it on Oracle 10, we get a "Type mismatch <..> expecting Float, actual Integer" error message.

Different customers use different versions of the Oracle platform, and we have a strict requirement that different versions of the application shall be backwards compatible. The number of queries involved is large. A simple search and replace of all floatfields converting them to integerfields is not feasible due to the backwards compatibility requirement and the differing oracle database versions at different customer sites. Also some of the floatfields actually are real floats. So we have a fairly complex situation.

I have been debugging a test application to try and find a way to solve this. The exception is raised from TDataset in DB.pas, from a protected, virtual method called CheckFieldCompatibility.

I tried to override this method and simply ignore the fact that the field definition was not the expected one, but the values shown in an ordinary dbGrid then came out completely wrong.

I then tried (still in the overridden CheckFieldCompatibility) to create a new field object with the same properties and events but with another field type, and delete the original field object. This also fails.

I tried a similar approach in an overridden version of the InternalOpen method, but at that point the FieldDefs are not available so this fails as well.

Is there anyone else with the same problem, and have you found a way to cope with this? Marco, will you please try to fix this?

With regards,
Helene

It is really an Oracle Client and/or Server issue, and a Borland VCL issue.

1. The field is reported with different scale and precision on different Oracle Server versions.

2. The TFieldDef class raises an exception when a persistent field does not match the database definition.

It's very unfortunate, but all you can do is avoid persistent fields in the specific case.

That is not a good solution for us either, since you must create persistent fields for ALL fields for a dataset, or for none. AND there are lots of property settings and event handlers set for the fields at design time.

I am posting a support case at Borland too for this problem. However, I am also testing another approach which seems to work. I override the doBeforeOpen method, detect the oracle version and if appropriate delete existing fields replacing them with fields of the correct type. This enables us to use persistent fields and maintain backwards compatibility.

With regards,
Helene

As I expected, Borland is not willing to see this as their problem either. They would rather have us look at ADO or dbExpress... which is not an option at all for us.

However, the approach described above does work quite well. Even if it is not the best way to solve the problem - I would have preferred a way to handle this at the dataset component level.

With regards, Helene

I have the same problem presented here when migrating to Oracle10. I get "Type mismatch <..> expecting Float, actual Integer" error message.

But Oracle reported at Note:241355.1 on metalink.oracle.com a similar problem.

I am referring to this following issue (taken from metalink):
"Bug 2235818 was backported to 9.2.0.3 and corrected the way in which columns that are of NUMBER type, but have no explicit scale specified, internally represent the scale (we return -127 instead of 0 now).
Unfortunately, lots of legacy code INCORRECTLY used a test on the scale and if it was -127 they assumed that the datatype was a FLOAT. The legacy code should have checked for a non-zero precision and a scale of -127 to detect a genuine FLOAT type.
As 9.2.0.3 is a patch set release it has been decided to offer event 10499 as a method to revert to the OLD behaviour. The OLD behaviour is enabled by using the event with any non-zero level.
N.B. The event is NOT available in 10i and so client applications that require the use of the event will need to be rewritten before they are moved to 10i."

That is not the same problem. This particular compatibility issue was addressed in 4.0.4.

I have been having the same issue and found one way to work around the problem (in reports at least). I changed the SQL in the TOracleDataSet component so that I cast the offending column into the correct datatype. After changing the SQL I deleted the field from the Fields collection and then added it back in. The format for the Cast I used is as follows:

Select CAST(quarter as integer) quarter,
fiscal_year_execution,
...
from myTable
Where blah blah blah

You can use float instead of integer or any other datatype (such as varchar2(x)).

Hope this helps

Rick Anderson

Hi all,
we've just encountered the same issue here...
We'd like to know if anyone has found a solution ?

We tried 2 solutions :
1 - setting TOracleSession.Preferences.IntegerPrecision to 10
=> ok with Oracle 9.2.0.7.......BUT NOT OK with oracle 9.2.0.1.
The pb is that our customers have different oracle versions...and our product must work in both 9.2.0.1 and in 9.2.0.7 oracle version...

2 - we tried to CAST as integer
=> good solution.........but only if you have less than 10 queries to modify.
In our product, it is not possible because we have thousands of queries...and we don't have much time to do such a correction...

Is there a new solution ?

Thanks in advance,
Laurent.