Re: [cx-oracle-users] Crash of OCIStmtRelease during cursor.close()

[Anthony T. <ant...@gm...>]

On Wed, Nov 3, 2010 at 9:27 AM,  <Gui...@su...> wrote:
> Hi, first off, thanks for your answer.

You're welcome.

>> 1) The latest version of cx_Oracle is now 5.0.4 which may or may not
>> have resolved this problem. I've never noticed it myself so wouldn't
>> be able to say.
>
> The reason we decided to use cxOracle 4.4 is because someone working with us two years ago already ported our product to 4.4 in one of our dev branches. Since 4.4 supports Oracle 11 and being in need of a quick solution, we decided to use that version.
> It might be worth looking into 5.0.4 but there will certainly be some regressions with our software, as with 4.4 at first, which could take time to solve with no assurance of seeing our original problem solved.

Ok.

>> 2) Not sure why you wouldn't simply call cursor.execute(stmt) over
>> cursor.prepare() and cursor.executemanyprepared(). Those last two are
>> only needed for specialized situations and your code doesn't appear to
>> require special processing.
>
> We call prepare() and then executemanyprepared() because calling prepare() allows us to check that cursor.statementType is not OCI_STMT_SELECT and to throw an exception if it is.

Just reacting to the code you were showing me. Realize that if you try
to fetch from a non-statement you will get a "not a query" exception
anyway so you could catch that. I also don't see any reason in the
code you demonstrated for executemanyprepared(). If that isn't
acceptable you can also retrieve cursor.description which will be None
in the case of a non-query. You can also quite easily call execute()
even after prepare(). The code behaves exactly as if you attempted to
execute a statement multiple times. The prepare phase is skipped.

All of these are just suggestions. Feel free to ignore any or all of them. :-)

>> 3) There is no need to call cursor.close() so you can try leaving that
>> out as well.
>
> Ok, but won't I get an "ORA-01000: maximum open cursors exceeded" error if I don't close it? Unless you're suggesting that we use the same cursor for the execution of all statements.

Once the cursor goes out of scope, it will be closed automatically. So
you won't get ORA-1000 errors unless you never allow the cursor to go
out of scope for some reason -- probably not a good thing anyway. :-)

> If this is what you mean, the thing is we have several hundred processes in our software that our actually much much more complicated than the loop I wrote in my first email, and more and more clients are getting this problem but never on the same processes. Changing all of them is not really an option as this would take weeks of development and extensive testing.

Of course. I wouldn't suggest that as a permanent solution -- just a
means of determining the source of the problem.

> I'd much rather know why cursor_close() is crashing than stop using it.

Naturally. So would I. :-) I almost never use cursor.close() and I
think most other people don't use it either so if there is a problem
with cursor.close() it doesn't show up often. After reviewing the code
a little more carefully I think I may have come up with a possible
cause, however. Are you able to build cx_Oracle yourself and would you
be willing to attempt a particular patch and test it in your
environment? The only other options are for me to build a binary and
send it to you for testing or for you to provide me a test case that
demonstrates the problem. I'd prefer the latter given a choice, of
course.

>> 4) Do you have multiple threads in your application that are accessing
>> Oracle? Database change notification? Either of those require threaded
>> = True as part of the constructor of the connection object.
>
> Yes, our application is indeed multithreaded but we are already opening a threaded=True connection.

Good.

Anthony