001 create or replace procedure UPDATE_MONITORING_RUNS(p_collection_name varchar2,p_runnumber number, p_status varchar2) is
002 v_rowexists NUMBER;
003 v_statusexists NUMBER;
004 v_oldstatus NUMBER;
005 v_runnumber NUMBER;
006 begin
007
008
009 select count(*) into v_statusexists
010 from monitoring_run_status mrs
011 where mrs.status_name = p_status;
012
013 if (v_statusexists = 0) THEN
014 raise_application_error(-20000, 'TAGS-009: This status does not exist');
015 end if;
016
017 select count(*) into v_rowexists
018 from monitoring_runs mr where mr.collection_name = p_collection_name and mr.run_number = p_runnumber;
019
020
021 if (v_rowexists = 0) THEN
022 insert into monitoring_runs
023 values (monrun_id.nextval, p_collection_name, p_runnumber, (select id from monitoring_run_status mrs where mrs.status_name=p_status));
024 commit;
025
026
027 insert into monitoring_log ml
028 values (monlog_id.nextval, (select mr.id from monitoring_runs mr where mr.collection_name = p_collection_name and mr.run_number = p_runnumber), 'INSERT', NULL, (select mr.status_id from monitoring_runs mr where mr.collection_name = p_collection_name and mr.run_number = p_runnumber), sysdate);
029 commit;
030
031
032 elsif (v_rowexists = 1) THEN
033
034 select mr.status_id into v_oldstatus
035 from monitoring_runs mr
036 where mr.collection_name = p_collection_name
037 and mr.run_number = p_runnumber;
038
039 update monitoring_runs mr
040 set status_id = (select mrs.id from monitoring_run_status mrs where mrs.status_name = p_status)
041 where mr.collection_name = p_collection_name and mr.run_number = p_runnumber;
042 commit;
043
044
045 insert into monitoring_log ml
046 values (monlog_id.nextval, (select mr.id from monitoring_runs mr where mr.collection_name = p_collection_name and mr.run_number = p_runnumber), 'UPDATE', v_oldstatus, (select mr.status_id from monitoring_runs mr where mr.collection_name = p_collection_name and mr.run_number = p_runnumber), sysdate);
047 commit;
048
049
050 else
051 raise_application_error(-20000, 'TAGS-009: Unique Key constraint violation');
052 end if;
053
054 end;
055 /
Due to the LXR bug, the updates fail sometimes to remove references to deleted files. The Saturday's full rebuilds fix these problems |
This page was automatically generated by the
LXR engine.
|
|