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

[<Gui...@su...>]

Hi, first off, thanks for your answer.


> 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.


> 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.


> 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. 

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. 

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


> 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.



Thanks for your help,
Guillaume


-----Message d'origine-----
De : Anthony Tuininga [mailto:ant...@gm...] 
Envoyé : mercredi 3 novembre 2010 14:50
À : cx-...@li...
Objet : Re: [cx-oracle-users] Crash of OCIStmtRelease during cursor.close()

Hi, just a few observations/suggestions

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.

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.

3) There is no need to call cursor.close() so you can try leaving that
out as well.

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.

Hopefully one of those resolves your issue.

Anthony

On Wed, Nov 3, 2010 at 5:09 AM,  <Gui...@su...> wrote:
> Hello,
>
>
>
> I am using cxOracle 4.4 on a Windows XP 32 bits platform with an Oracle 11.2
> client and a distant Oracle 11.2 server.
>
>
>
> The problem I am getting since we migrated our software to Oracle 11 is that
> during one of our long running processes, the software will sometimes
> suddenly crash with no exception raised.
>
> Our process calls a PL/SQL package hundreds of thousands of times and
> sometimes crashes after one of those calls has ended, in the middle of
> cursor.close().
>
> I determined that inside Cursor_Close(...) it is Cursor_FreeHandle(...) and then
> the call to OCIStmtRelease that crashes.
>
>
>
> Here's a simplified view of what we do:
>
>
>
> for(~200 000 times){
>
> crs = Cursor()
>
> crs.prepare(stmt) # stmt is a call to a PL/SQL package
>
> crs.executemanyprepared(1)
>
> crs.close() => sometimes crashes in Cursor_FreeHandle during call to
> OCIStmtRelease, never in the same iteration of the loop
>
> }
>
>
>
> The thing is that this crash does not happen systematically but rather in
> about one out of two executions of our process.
>
>
>
> We previously used cxOracle 4.1.1 but moved to 4.4 when noticing this
> problem with Oracle 11. But this 4.4 version doesn't seem to be solving the
> problem, unless we need to make more changes to the way we use cxOracle
> after migrating to 4.4.
>
>
>
> Does anybody have a clue as to why OCIStmtRelease would sometimes
> unexpectedly crash like that without throwing any exception?
>
>
>
> Thanks a lot for the help you can give me. I've been working for more than a
> month on this problem and it's becoming critical for our clients that a
> solution be found.
>
>
>
> Guillaume Aouizerate