OracleSession.LogOn Freeze

[cptngrb]

Oracle Client version 12.2 win x64; DOA 4.1.3; Delphi RIO.

I use a session pool to connect to the DB. There are several (20-30) threads running in the app. Each thread has its own TOracleSession. The connection to the database is not stable. The server may restart. And in these conditions, sometimes when OracleSession.Logon, the thread sometimes freezes. No errors or other messages.
When the connection or server is restored, usually OracleSession.Logon returns some result, but sometimes nothing happens. But if you restart the app, everything works as it should.
What can I do about it?

 

[mglass]

When the server goes away without telling the client, the client has to wait for the connection to timeout when it sends another command (which includes any attempt to determine the state of the connection).

Have you tried using Logoff (or Connected := False) before using Logon?

There are client settings that determine the sql timeout for the connections.

 

[cptngrb]

I use the following sequence of actions in the thread (method Execute)

while not Terminated do begin
OracleSession.LogOn;
OracleQuery.Execute;
OracleSession.LogOff;
end;

P.S. Taking sessions from the pool

 

[cptngrb]

How do I set the client settings for timeout?

 

[cptngrb]

except through editing file tnsnames.ora

 

[cptngrb]

When the server stops responding and I try to connect via OracleSession.Logon, 18 of the 25 threads hang for an indefinite time, and only 7 threads return the error "ORA-12154: TNS:could not resolve the connect identifier specified", after 10 minutes, a couple more threads return the error, but the rest remain unavailable.

 

[mglass]

The client file sqlnet.ora controls the timeout.

SQLNET.OUTBOUND_CONNECT_TIMEOUT=10
SQLNET.SEND_TIMEOUT=10

"ORA-12154: TNS:could not resolve the connect identifier specified" indicates that Oracle didn't know where to connect to. You would get a slightly different error if it cannot connect to the server (ie. it resolved the name but couldn't connect).