Oracle 9.2.0.5 and views

[vwilhelm]

Hi,

We have installed our first version of oracle 9.2.0.5 and discovered a serious problem with SQL-statements which use views, especcially
views in conjunction with unions.
Some field of the view have changed their datatype from number to number(4), which translates to integer. At runtime this is detected by the DOA components who raise an error like 'float expected but integer found''. I know I can force the DOA
to always use float fields, but this is not a capable way because our financial software has literally hundreds of forms and datamodules.
Reports would have to be redefined also, who want's a customer-id with a precision

Is there any solution which avoids to change a lot of modules ?

Greetings
Volker Wilhelm
COMPU-ORGA GmbH

 

[Marco Kalter]

If all is well this problem is fixed in Direct Oracle Access 4.0.4.

In Oracle 9.2.0.4 and later the interpretation of scale and precision has changed somewhat, which can cause these effects.

 

[maxclot]

D7 professional, DOA 4.0.5, Oracle 9.2.0.5, InfoPower 4000.05

SELECT *
FROM (SELECT 'TMP' AS TIPO_TAB,
A.PORTAFOGLIO, A.NUMERO_ARTICOLO, B.PROGR_RIGA_ARTICOLO AS RIGA_ARTICOLO,
...
FROM PNOTA_ART A,
PNOTA_RIGART B
WHERE A.NUMERO_ARTICOLO < 0
AND B.NUMERO_ARTICOLO = A.NUMERO_ARTICOLO
AND B.PORTAFOGLIO = A.PORTAFOGLIO

UNION ALL
SELECT 'DEF' AS TIPO_TAB,
A.PORTAFOGLIO, A.NUMERO_ARTICOLO, B.PROGR_RIGA_ARTICOLO AS RIGA_ARTICOLO,
...
FROM PNOTA_ART A,
PNOTA_RIGART B
WHERE A.NUMERO_ARTICOLO > 0
AND B.NUMERO_ARTICOLO = A.NUMERO_ARTICOLO
AND B.PORTAFOGLIO = A.PORTAFOGLIO)
WHERE RIF_DOCUMENTO = :RIF_DOCUMENTO
AND PORTAFOGLIO = ORTAFOGLIO

This is the SQL property of a detail dataset.
This SQL works fine in Oracle SQL Plus (any release).

NUMERO_ARTICOLO is a NUMBER(9,0) field in PNOTA_ART and PNOTA_RIGART tables.
Usually Delphi see it as an integer TField but, after the 'union all', Delphi see it as a float TField. This has always occured in Delphi, no matter the Oracle release you were using.

On the create event of my form, I open the master table (OK!) and then the detail table (the SQL above). On this statement I get the following error message: 'Tblxxx: type mismatch for field 'NUMERO_ARTICOLO'; expecting float, actual integer'.
With the Oracle monitor I can see that this query returns the three rows I'm expecting before the abend.
I tried to manage this dataset without a master/detail approach, but I always get this error.

My program works fine from Oracle 8.1.7 up to Oracle 9.2.0.3, not in Oracle 9.2.0.5. I agree with your consideration about a different interpretation of scale and precision in Oracle 9.2.0.4 and later that causes this incompatibility but, as you can see, the problem has not been solved in DOA 4.0.4

Any suggestion?
Thanks

 

[al0]

Hi Marco

Originally posted by Marco Kalter:
If all is well this problem is fixed in Direct Oracle Access 4.0.4.

In Oracle 9.2.0.4 and later the interpretation of scale and precision has changed somewhat, which can cause these effects.

We have encountered the same problem. Program is based on DOA 3.4.6 (for historical reasons as it is very big it is unlikely to move to DOA 4.
It works perfectly agains Oracle 9.2.0.4 but not agains 9.2.0.5 - the same integer/float problem.
The setting of EVENT 10499 in oracle.ini that is supposed to restore an old behaviour doesn't help.

Can you suggest something?

Yours sincerely,

Oleksandr Alesinskyy

 

[Marco Kalter]

We're looking into this 9.2.0.5 issue.

 

[al0]

Originally posted by Marco Kalter:
We're looking into this 9.2.0.5 issue.

Thank. Hope you will be able to find something fast

 

[Syspec]

Volker Wilhelm,

You said that you had a way to force the DOA to always use float fields. What is this and can I just use it for one dataset/SQL query?

Thanks,
Christopher.

 

[igorilia]

Use TOracleSession.Preferences.IntegerPrecision = 1 ( or -1 better ). All fields wider then 1 will be TFloatField

 

[Syspec]

Thanks Igorilia,

I've only had one instance of the problem so far and so I resolved it by using:
SELECT to_number(to_char(A.CODE))
FROM (SELECT CODE
FROM ...
)

 

[al0]

Originally posted by al0:

Originally posted by Marco Kalter:
We're looking into this 9.2.0.5 issue.

Thank. Hope you will be able to find something fast

Hi Marco!

Have you any progress on this issue?

 

[Marco Kalter]

Not yet, I will let you know when we have a patch.

 

[al0]

Originally posted by Marco Kalter:
Not yet, I will let you know when we have a patch.

And now? Any progress?

 

[Marco Kalter]

We hope to have a work around in 4.0.6, which should be available early next month.

 

[al0]

Originally posted by Marco Kalter:
We hope to have a work around in 4.0.6, which should be available early next month.

FYI, Oracle has recognized (on my insistence) this behaviour change of 9.2.0.5 as bug and has assigned bug number 3807954 to it. There is some hope that fix from Oracle will be available at late September.