Question Losing connection to Oracle when running long-running procedure from .NET

[tgvbdotnet]

I am trying to execute a long-running Oracle procedure from .NET. The procedure takes about three hours to finish. Ideally, I would like the user to be able to close the browser and come back later to check on the results. The problem is that the connection to Oracle is lost after exactly an hour (The execution stops with ORA-3113 - end-of-file on communication channel). As you would expect, the procedure finishes when run from SQL Plus. Strangely enough, it also runs to completion if I set a breakpoint in my .NET code after the procedure is invoked (I kick off two asynchronous threads, one of which executes the procedure. I set a breakpoint on the second).

My connection string:
data source= (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=server123)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=SID_abc)))

Some relevant lines from web.config:
<sessionState mode="InProc" cookieless="false" timeout="3000" stateNetworkTimeout="72000"/>
<httpRuntime executionTimeout="18000" maxRequestLength="2097151" />

Any thoughts as to why the connection would be lost after an hour, and what I can do to maintain the connection till the procedure finishes?

Thanks,

Tom

 

[cjard]

For something like this, i'd create a non-repeating job in the oracle scheduler and then run it upon request of the user
Oracle DBMS_SCHEDULER Job Scheduling

PS; 3 hours? My co doesnt allow queries that run for longer than half an hour and even that causes problems. Have you made the query as fast as it can go?

 

[tgvbdotnet]

That should work! Great idea - thanks!