Re: Process a huge SQL procedure before timeout with JSP

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:<9i5f9d02taj_at_drn.newsguy.com>...

> 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.

Thanks for your help (btw, I'm impressed by the number of oracle answers I see you write on google...keep it up). I'm not sure what you mean by instrumenting with DBMS_APPLICATION_INFO, but I did get to submitting with DBMS_JOB. It looks like:

SQL> declare
  2 l_job number;
  3 begin
  4 dbms_job.submit l_job,
'LEWIS_CLARK.MergeIntoNew(''emptyset'',''emptyset2'',''emptyset11'');');   5 commit;
  6 end;
  7 /

or in the JSP:

String procCall = "DECLARE l_job NUMBER; BEGIN DBMS_JOB.SUBMIT(l_job, 'LEWIS_CLARK.MergeIntoNew('' ... '');'); COMMIT; END;";   CallableStatement cs = con.prepareCall (procCall); cs.executeUpdate();

On executing either of these I get a "PL/SQL procedure successfully completed" message, but there is no feedback from the procedure (the procedure inserts data into a table in the database, so I could tell if it was working by querying that table). Are there any glaring errors here that I'm not seeing? Thanks --

Dane Received on Tue Jul 10 2001 - 16:06:53 CDT