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.

  • This is probably configured in your TNSNAMES.ORA – Gaius Jan 20 '19 at 12:59
  • 1
    @Gaius: That looks like it might be it, I will test it. You should write that as an answer because I will accept it if it works and if not it might help someone else! – Malvineous Jan 20 '19 at 14:07

As a workaround a trigger was added to the user account, to run the ALTER SESSION command upon every connection. This has fixed the problem, however we are still unsure as to why the session transparently reconnects without any notification to the client app!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.