Procedure with OUT-param as sys_refcursor fails after x time

goethals.f · Post by **goethals.f** » Tue 18 May 2010 15:05

Hello hello,

I feel like I have to deal with some very strange behaviour in my app using dotconnect en Oracle client (10.2)

I have a simple stored prodecure with only one out parameter as type sys_refcursor (in the procedure, only a simple SQL statement is code there.

PROCEDURE SELECT_LIBRARY_GEOMETRY (
p_ResultSet OUT sys_refcursor
)
OPEN p_ResultSet FOR
SELECT ...

END SELECT_LIBRARY_GEOMETRY;

When I now execute this storedprocedure in my application using dotconnect en oracle client mode (direct == false)

I simply execute a stored-procedure with no parameters filled in (and parametercheck == true) I do not add the output-parameter myself, because the support-guys told me the output params are populated automatic.

But now, After some iterations of
oracleCommand.ExecuteNonQuery()

--> the parameter-value of the output-parameter is suddenly "null" instead of a real "Devart.data.oracle.oraclecursor".

Remarks: I only create the oracleCommand instance once, and tested it with a one-time prepare() call or with everytime a prepare() call before ExecuteNonQuery --> but problem stays the same.

And the really strange part is, if I change over to direct mode in connectionstring (and don't change any code) --> I never have the above problem ???

So, is there some strange behaviour possible or restrictions applied when using stored-procedures with cursor-output parameters and the oracle client itself.

A possible solution could be to switch to direct-mode (but then I have to deal with the direct-mode limitations) and I there must be a correct way to execute the same procedure several times (with the least performance hit of creating objects, parameters checks, hard/soft parsings, caching of statements, ...)

Does anyone have some clever tips or hints ?

thx
Fred

[/b]

StanislavK · Post by **StanislavK** » Thu 20 May 2010 09:09

We've reproduced the problem with the 5.35.79 version of dotConnect for Oracle, but couldn't with the latest 5.60.124 version.

Please try using dotConnect for Oracle 5.60.124 and tell us if this helps.

goethals.f · Post by **goethals.f** » Thu 20 May 2010 09:43

StanislavK wrote:We've reproduced the problem with the 5.35.79 version of dotConnect for Oracle, but couldn't with the latest 5.60.124 version.

Please try using dotConnect for Oracle 5.60.124 and tell us if this helps.

Hi,

I have retested my test-app with the new 5.60.124 release.

On my current PC I have two oracle clients (10.2 and 8.1 for acces to oracle 7.3.4 with OraHome81 as default home)

*) Testing the app in direct-mode --> still works (worked before with previous version)

*) Testing the app with oracle10 client --> I did not have the error (tried with a 100-for-loop). In older version after 5 times already crash. So it seems that version 5.60.124 works

*) Testing the app with Oracle 8 client (accessing the same oracle 10g server) --> I still receive the error in my other post (see: http://www.devart.com/forums/viewtopic. ... highlight= )

So my problem is indeed partially solved.

I can use my application now with the new 5.60.124 version --> but this means I have to install + configure on all our PC's the extra oracle 10 client (they only have oracle81 now)

It would be perfect, if the error in the post (http://www.devart.com/forums/viewtopic. ... highlight=) could be solved nearly in a new 5.60... build. So it still keeps working with the older oracle81 client.

Do you have already tackled the problem in the other post (because there would be a fix in several days you posted on the 5th of may?)

But at least, thx for pointing me out to the new version and to the info you gave about multiple homes in the other threads...

Fred

goethals.f · Post by **goethals.f** » Thu 20 May 2010 10:06

As a extra remark:

To fully understand my app requirements:

The application itself should be able to :

acces data from Oracle10 (which now need oracle10 client + version 5.60.124)

+

acces still old data from oracle734 (which can not be accessed by oracle10 client, so we use oracle8 because this client is able to acces both DB servers). But version 5.60.124 is currently not compatible with oracle8 client.

so - in my opinion - the only way my app can work now and having 2 connections is:

*) using 5.60.124
*) Oracle10 client installed
*) Oracle8 client installed (is mandatory because of a bunch of other legacy tools)
*) connecting to oracle734 via Direct-mode using 5.60.124 --> which means we have to consider the direct-mode limitations...

Fred

StanislavK · Post by **StanislavK** » Thu 20 May 2010 16:24

Glad to see that the problem was resolved.

As for the second problem, the fix will be available in the nearest build. We will release it next week.

cerealjoe · Post by **cerealjoe** » Wed 11 Aug 2010 03:29

StanislavK wrote:We've reproduced the problem with the 5.35.79 version of dotConnect for Oracle, but couldn't with the latest 5.60.124 version.

Please try using dotConnect for Oracle 5.60.124 and tell us if this helps.

Thank you for reproducing the new version. I hope I can also used the new version.