Stored Proc call fails after upgrade from Oracle 12 to Oracle 19

[clint_silver]

Discovered an issue with OCI calls to certain stored procedures from Oracle 18 onwards. We encoutered it after upgrading from 12 to 19. We have many procs that are called and work, the stored proc thats failing accepts arguments of TYPES defined as TYPE table. The error manifests itself as

Code: Select all

{"Cannot describe stored procedure arguments."}
 
        StackTrace  "   at Devart.Data.Oracle.OracleSessionOci.DescribeProcedure(String \u0002, Int32 \u0003, OracleConnection \u0005)\

Which leads us to an introduced change from 18c onwards in PLSQL with which how OCI calls to Oracle procedures work as they refer to ALL_TYPES and ALL_ARGUMENTS dictionary view to asses the object before calling and Im guessing the multiple TYPES are now not returning as expected on the call.

https://docs.oracle.com/en/database/ora ... B8630FC32D

We can get rid of this error by mimicing the previous Oracle 12 version of the ARGUMENTS view by setting events

alter session set events '10946 trace name context forever, level 65536';

(we dont want to apply to entire system so just setting on session for now)

And if we call this before the call to proc and recompile the procedure, it gets it to work.

But this is a workaround. Is this known about in OCI land and is there any plans to fix it?

Curently tested on Devart 9.11.980

[DmitryGm]

Please describe in more detail: what steps do we need to take to reproduce this error in our environment? Could you share a dump of the database with your procedures, as well as an example of calling those procedures that causes the error?

Also, note that the latest version of dotConnect for Oracle 9.14.1369 contains some improvements and fixes:
https://www.devart.com/dotconnect/oracl ... story.html

[clint_silver]

I'll put a test case together so you can replicate

[peterk]

I can confirm both
- the problem still exists even with the latest version (9.14.1382)
- the proposed workaround.

Are there any news on this issue?

[DmitryGm]

I'll put a test case together so you can replicate

Are there any news on this issue?

We are still waiting for the test case to reproduce the issue.

Please describe in more detail: what steps do we need to take to reproduce this error in our environment? Could you share a dump of the database with your procedures, as well as an example of calling those procedures that causes the error?

[peterk]

We are still waiting for the test case to reproduce the issue.

I just sent you a mininmal test case per mail.

[DmitryGm]

Thank you for your report. We have reproduced the issue (Cannot describe stored procedure arguments) and the investigation is in progress. We shall inform you as soon as we have any results.

[peterk]

Hi Dimitry,

any progress on this issue?

Kind regards

Peter

[DmitryGm]

the investigation is in progress. We shall inform you as soon as we have any results.