Fix sporadic Oracle Automation Erro

See more:

Oracle

I have a VB6 application which uses Oracle Objects for OLE’s batch insert logic. It can run for days, processing thirty or more files a day. But every so often, it fails on one of the ExecuteSQL statements.

The error is -2147417848: Automation error. The object invoked has disconnected from its clients.

I cannot recreate it by choice but, if I run hundreds of files through the application in the development region, it will eventually error out. I’ve checked the LastServerErr from the OraDatabase and from the OraSession and they are both zero.

I’ve tried closing the database connection and letting the code continue but then the next database access gets a new error:

Unable to make connection, ORA-12545: Connect failed because target host or object does not exist.

I don’t think it’s a data related problem because, when I stop and restart the application, the same file which failed, runs through perfectly. And I don’t think it’s a number of files or records issue because sometimes it takes days to fail and sometimes it will fail twice within two hours.

I’ve googled my little fingers off with no results. My next idea was to have this failing application, when it hits this error condition, start up another copy of the executable and then end the failing one. It should work but there’s got to be a better way, right?

Any help would be much appreciated.

Here’s the code used to make the Oracle connection:
Set poOraSession = CreateObject("OracleInProcServer.XOraSession")
Set poOraDatabase = poOraSession.OpenDatabase(strDSN, strUserID & "/" strPassword, 0&)