Re: [cx-oracle-users] OCI_ATTR_SERVER_STATUS and cx_oracle

[Maciej W. <mac...@co...>]

> Hmm, I guess one possibility is to check this attribute immediately
> upon receiving an error in cursor.execute and close the connection if
> such an error is received. 
Right, something like that, if only I'm right about correlation between
OCI_ATTR_SERVER_STATUS and ORA-3113, ORA-3114 etc. errors.
I found this:
http://forums.oracle.com/forums/thread.jspa?threadID=435600
and this doesn't seems good... :(

In general this is silly (or I can't find any documentation, except one
about OCI)
that Oracle doesn't say what errors should be handled by applications.
Maybe connection pools shouldn't be used with Oracle, or what?

If there is no correlation between OCI_ATTR_SERVER_STATUS
and errors that need reconnect, then maybe it is
good idea that cx_oracle can somehow signal this situation to the
application?
special exception type  (possibly configurable somehow if it should be
raised)
or kind of marker that will be set on connection object when cursor.execute
ecounters this error. For me it would be nice to have this feature in
one place
(cx_oracle) and not in all pool implementations again and again.

It is common. For example I found that there are/were some problems with
.NET pools:
http://support.microsoft.com/kb/830173

> I'm not sure how much good this is going to
> do you, though -- you will simply get a different exception
> (connection is closed Python exception as opposed to connection not
> connected Oracle error)! 
> Another possibility is to check the error code in the execute of a
> subclassed cursor and do the appropriate thing in your application as
> a result -- this is application specific, of course.
>
> Perhaps if you gave me some indication of how this might be put to use
> or how the different exception would be superior I might be able to
> determine how best to make use of this information. As you surmised,
> the OCI code to do this is very simple indeed. I'm simply lost on how
> to make this information useful. Help wanted. :-)
Usually errors like ORA-03113 are per connection. So closing
this connection and opening new one allows application to continue
work.

In my case:
If I get database exception after call to cursor.execute(...),
then I can check OCI_ATTR_SERVER_STATUS (via cx_oracle
function I hope :) ) and if this says I have to reconnect I'll
close the cx_Oracle connection and... raise
Retry(...) exception which is special Zope publisher
exception used to retry whole request. Retry may be called
only 3 times for same request. Retrying request means that
Zope will abort all changes done during this
unfinished request, and will start new request that is identical
to previous one.
During retried request Zope will find there is no connection to
oracle (as I've closed this a while ago) and it'll open the new one.
This way I have transactional integrity and user sees nothing
in his browser... and I can just write exception warning to the logs.

If it will be not possible to reconnect then, as you said, I'll get
exception like 'Not connected to oracle', but usually it is
possible to reconnect with these specific oracle errors as
they're sometimes caused by network failure/timeout, killing
oracle sessions on the server side and such things.

-- 
Maciej Wisniowski