Tom,
I have put the actual code here.
1. Trigger
CREATE OR REPLACE TRIGGER TRG_SEGMENTATION_DATA_REFRESH
AFTER INSERT OR UPDATE ON SEGMENTATION
BEGIN
DBMS_ALERT.SIGNAL('SEGMENTATION_ALERT','SEGMENTATION');
END;
/
CREATE OR REPLACE TRIGGER TRG_SERVICE_CLASS_DATA_REFRESH
AFTER INSERT OR UPDATE ON SERVICE_CLASS
BEGIN
DBMS_ALERT.SIGNAL('SERVICE_CLASS_ALERT','SERVICE_CLASS');
END;
/
-- There are 26 more tables with similar trigger code
2. Procedure
CREATE OR REPLACE PROCEDURE refresh_cst_static_data
AS
v_http_data VARCHAR2(2000);
v_name VARCHAR2(60);
v_message VARCHAR2(200);
v_status NUMBER;
v_static_url VARCHAR2(200) := '</code>
http://cst.zoneld.net/ecare/reloadStaticData/'; <code>
v_refresh_url VARCHAR2(250);
BEGIN
DBMS_ALERT.REGISTER('SEGMENTATION_ALERT');
DBMS_ALERT.REGISTER('SERVICE_CLASS_ALERT');
--This proc registers for 26 other alerts in the same way. I have removed those statements.
LOOP
DBMS_ALERT.WAITANY(v_name, v_message, v_status, 60);
IF v_status = 0 THEN
v_refresh_url := v_static_url||v_message;
begin
v_http_data := utl_http.request(v_refresh_url);
exception when others then
null;
end;
ELSE
exit;
END IF;
END LOOP;
DBMS_ALERT.REMOVEALL();
END;
/
3. This is the script for creating the job
declare
l_job number;
begin
dbms_job.submit( l_job,
'begin
refresh_cst_static_data;
end;',
sysdate,
'sysdate' );
commit;
end;
/
The URL points to a sevlet (on 9ias) which does the refresh of static data.
Servlet is selecting data for the table specified in the URL.
February 17, 2004 - 7:54 am UTC
ahh, got it.
When you call removeall -- it is removing the alerts in order of registration. Do this:
IN SESSION 1:
declare
v_name VARCHAR2(60);
v_message VARCHAR2(200);
v_status NUMBER;
begin
dbms_alert.register( 'A' );
dbms_alert.register( 'B' );
DBMS_ALERT.WAITANY(v_name, v_message, v_status,1);
end;
/
IN SESSION 2:
exec dbms_alert.signal( 'B', 'x' );
IN SESSION 1:
exec dbms_alert.removeall();
<that'll block -- but it'll have locked "alert A" in the removal and will be stuck on removing "alert B">
IN SESSION 2:
exec dbms_alert.signal( 'A', 'x' );
<that'll block -- and the other session #1 will get:
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "SYS.DBMS_ALERT", line 464
ORA-06512: at line 1
They are locking each others resources here.
Also, since ALERTS are totally "blocking city" sort of things (eg: only one session at a time can fire an alert of a given name!!!!) you have serialized all access to these 26 tables (surprised you do not have deadlocks all over the place actually).
A suggestion, create a table:
create table job_parameters( job_id number primary key, url varchar2(255) );
create index job_parameters_idx on job_parameters(url);
and use a trigger like this:
CREATE OR REPLACE TRIGGER TRG_SEGMENTATION_DATA_REFRESH
AFTER INSERT OR UPDATE ON SEGMENTATION
DECLARE
l_job number;
BEGIN
dbms_job.submit( l_job, 'run_url(JOB);' );
insert into job_parameters(job_id,url) values
(job_id,'http ://cst.zoneld.net/ecare/reloadStaticData/' );
END;
/
and code:
create or replace procedure run_url( p_job_id in number )
as
l_rec job_parameters%rowtype;
l_http_data long;
begin
select * into l_rec from job_parameters where job_id = p_job_id;
delete from job_parameters where URL = l_rec.url;
commit;
l_http_data := utl_http.request(l_rec.url);
exception
when no_data_found then NULL; -- just skip it, we didn't need to run again
end;
/
In that fashion -- N number of concurrent sessions can request that URL to be run at the same time.
The job will run -- pick up the URL, get rid of any duplicate URL's queued up, then run the URL. The other jobs will just run and exit -- until something appears in the job parameters table again.