oracle-databasedelphibdeora-12560

BDE multi-thread error: when encounter ora-12560, cannot restart TDatabase


We have a big program, using BDE + oracle, developed by delphi, We use 3 threads to do DB operations, they are using separated TSession. (1 deamon thread, 1 data collect thread, 1 checking thread)

We use this structure:

TQuery -> TDatabase -> TSession

We use this method to do query:

try
  qu.close;  //TQuery
  qu.sql.clear;
  qu.sql.add('select foo from db_foos');
  qu.open;
except
  on e:Exception do
  begin
    db.close; // db is TDatabase, We use this to auto restart connection when network fail.
    error(e.message);
  end;
end;

the problem is, when our program run more than 24 hours, ora-12560 arise, and our program cannot restart connection, and keep report this error when execute queries.

We had reviewed code, debugged all week long, and still cannot solve it, do you guys encounter the same problem?

edit:

We have this test case:

3 program, each run one thread(with 1 second sleep), and between 0.5 - 1 hour, They all failed at the same time. (3 DB operation, and 1 Indy9 ftp client...)

So I think there is something wrong in BDE or Windows socket... Current I am working on a auto restart program machanism as a workaround.


Solution

  • Apparently it's a common problem where the Oracle client forgets where it's supposed to be connecting. Perhaps one of these will help:

    http://www.cryer.co.uk/brian/oracle/ORA12560.htm

    http://www.dba-oracle.com/t_ora_12560_tns_protocol_adapter_error.htm