Re: Process a huge SQL procedure before timeout with JSP

In article <199a58b3.0107061344.2365f1c4_at_posting.google.com>, junkmailblackhole_at_yahoo.com says...
>
>I'm running a SQL stored procedure on JSP server Resin 1.2.5. The
>stored procedure takes two columns of data and merges them into a
>third. It times out after an hour of processing time. I've tried
>increasing the "usertimeout" variable in Resin's <database
>name>.properties file. That worked to get me from a one minute
>timeout to a one hour timeout, but there must be another timeout
>counter somewhere that's not letting me past one hour. Is there any
>way that I could either lengthen that indefinitely or else process the
>stored procedure outside of the JSP page and then send the results to
>the JSP page when it's finished? Thanks
>
>Dane

A process that takes over an hour is not a good candidate for a JSP.

What I would do is:

• instrument the process with calls to DBMS_APPLICATION_INFO, allowing it to set values in the action, module and client_info columns in V$session and the more columns in v$session_longops
• have the jsp submit the procedure to be executed using dbms_job
• have a screen that uses a meta tag every minute or so to query v$session and v$session_longops to report the processes status and its estimated time to completion.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp