| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: sequential output with OWS
A copy of this was sent to "Simon Kerr" <kerrsim_at_netconnect.com.au>
(if that email address didn't require changing)
On Tue, 2 Mar 1999 14:03:19 +1100, you wrote:
>Hi,
> I am using OWS 3.0.1, and I have a package that fires off a stored
>procedure for every row in a table. The SP in question takes ~2 minutes for
>every row, so as you can imagine, the web browser gives up after a short
>time and says that the page does not exist. Is there any way I can output
>something like "completed customer XXXX" after each row has been processed?
>so that the browser has something displayed on it (instead of waiting for
>the SP to complete) and the users can tell something is going on.
>
>thanks,
> Simon Kerr
>
I don't think you want to run that procedure from the web -- you might initiate it from the web, but you should probably run it in the background or something. If the table has 5 rows -- that takes 10 minutes. The browser will hate that.
What you might consider doing instead is setting up the job queues (see the file $ORACLE_HOME/rdbms/admin/dbmsjob.sql for some info on that) and using the job queues to submit the job in the background. That way, this long running job is not reliant on a client -- it just runs in the background -- and if this job writes status information either into v$session (see the dbms_application_info package) or into a report table of some sort, then another session can monitor its progress.
I'm thinking of something like this:
create or replace procedure long_running_procedure( p_x in int,
p_job in int )
as
for i in 1 .. p_x loop
dbms_application_info.set_module( 'long job', p_job );
dbms_application_info.set_client_info( 'step ' || i );
dbms_lock.sleep( 10 );
create or replace procedure run_long_running_job( p_iterations in number ) as
l_job_id number;
begin
dbms_job.submit( l_job_id,
'long_running_procedure( p_x => ' || p_iterations || ', p_job => JOB );');
htp.p( '<HTML>
<HEAD>
<META HTTP-EQUIV="Refresh"
CONTENT="10;URL=/wa/intranets/owa/tkyte.show_status?p_job='||
l_job_id||'">
<TITLE>Your Job</TITLE>
</HEAD><body>Your Job ' || l_job_id || ' has been submitted..
</body></html>'
);
create or replace procedure show_status( p_job in int )
as
begin
htp.p( '<HTML>
<HEAD>
<META HTTP-EQUIV="Refresh"
CONTENT="10;URL=/wa/intranets/owa/tkyte.show_status?p_job='||
p_job||'">
<TITLE>Your Job</TITLE>
</HEAD><body>Your Job ' || p_job || ' has been submitted..'
);
for x in ( select *
from v$session
where module = 'long job' and action = p_job )
loop
htp.bold( '<br>Processing ' || x.client_info || '<br>' );
end loop;
htp.p( '</body></html>' );
end;
/
so, you write long running job and then run_long_running_job will submit it and then cause a page on the client to be refreshed (in this case) every 10 seconds-- passing in the job number of the submitted job. The long running job reports its status in the v$session table via the dbms_application_info call and we can show the status of the long running job. The neat thing here is that you can actually stop the browser, goto another terminal and view the status of you job (you just need the job number) as it runs...
note: for the above example to work you will need execute on dbms_lock granted DIRECTLY to you as well as SELECT on SYS.V_$SESSION granted DIRECTLY to you. A role won't do it, must be directly to you.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |