Oracle 11 to Oracle 12 upgrade

[mikeho]

Any good lessons learned or best practices when using ODAC and upgrading from Oracle 11 to Oracle 12?

Any settings within ODAC that should be looked at?

Any Delphi coding changes that should be considered?

[AlexP]

Helllo,

If the database structure has not been changed, no additional changes will be required in ODAC or in your code.

[mikeho]

Testing after upgrade to Oracle 12 and getting a type mismatch error for a field. Expecting integer Actual: float. Why would that be coming up now? The field length for the field in question is Number(9).

The same query works fine in Oracle 11.

[MaximG]

We tested ODAC work according to the description and found no problems. We used the table with the NUMBER(9) type field in Oracle 11 and Oracle 12 and got the same results when working with both DBMS.
For further problem investigation, please provide DDL script to create the table which you are using in your project, as well as the snippet causing the described error.

[mikeho]

That will be impossible to do with the size of our DB, but I can provide you the scenario.

TOraTable with Wave field number(9) (Data comes back fine)

TOraQuery Master that pulls from a view, Master Source is TOraTable with Wave parameter supplied by table. (Data comes back fine)

TOraQuery Child that pulls from another view, master source is Master with Wave parameter supplied by Master. This is the one that gets the mismatch error.

[mikeho]

Update. Seems to be an known Oracle 12c issue.

https://community.oracle.com/thread/4014403

[MaximG]

We are glad that you could identify the problem. Contact us on any questions concerning ODAC usage.

[mikeho]

Our DBAs applied the Oracle patch, but that did not resolve the issue. I sent the information via the Devart Request Support Contact Form along with an attachment. I am hoping your system received it, because requested a copy of the email be sent to me, but I never received a copy. Can you confirm receipt?

[MaximG]

We received the problem description via Devart Request Support Contact Form and started to investigate it.

[mikeho]

Sent further information via email. Can you confirm receipt?

[MaximG]

We received your message via email, however we could not get the mentioned ZIP file

[mikeho]

Maxim I sent one directly to your email, Did that not have the zip file either?

[MaximG]

Thank you for the description. We received your sample and started to investigate the problem. We will inform you about the results in the nearest time

[mikeho]

FYI, testing with TCP/IP Direct connection gives some strange results:
Adding the fields to the Fields editor, the Wave field comes back as TIntegerField at design time. However, at runtime with Direct connection, still get the Type mismatch error.

[mikeho]

For any others having similar issues, this was resolved with an Oracle setting:
alter session set "_optimizer_reduce_groupby_key"=false;