I have an application I wrote that uses OCI to connect to an Oracle database. The database has recently been upgraded to Oracle 18, and since then database searches in the application randomly stop working.
I have narrowed the problem down to this. When the app connects to the DB it runs some
ALTER SESSION commands to disable case-sensitivity in
LIKE conditions (so that I can search for "smith" and still find "Smith"). After some hours, the network drops the TCP connection. The next morning when someone tries to access the system, the Oracle client transparently reconnects and executes the query.
The problem is that this reconnection happens without my code knowing about it, apparently by the Oracle client itself, and it does not re-run the original
ALTER SESSION commands after reconnecting. Our DBAs have supplied me with an audit log that shows the session ID changing, a new
LOGON attempt and a single
ALTER SESSION running to set the timezone (which does not come from my code, and also appeared in the original connection).
This means that my application issues queries to the database as per normal, unaware that there has been any interruption, but suddenly they become case-sensitive and the users start complaining that the search isn't returning any results.
With the previous Oracle server version my code would receive an error if it had been disconnected, and the code would try to reconnect and as part of the reconnection, it would run the
ALTER SESSION commands again so that there was no change to the behaviour of the system.
I can't find anything about this auto-reconnect feature or how to adjust it, so is there some way to either preserve the
ALTER SESSION changes so they are re-applied after the reconnection, or to disable this behaviour and return an error to the code if the connection is lost as used to happen?
The client is an older 11.2 one and upgrading it isn't really practical, but since it worked before the server upgrade I would've thought that version would be ok.