create or replace procedure UPDATE_MONITORING_RUNS(p_collection_name varchar2,p_runnumber number, p_status varchar2) is
 v_rowexists NUMBER;
 v_statusexists NUMBER;
 v_oldstatus NUMBER;
 v_runnumber NUMBER;
 begin
 
 --check if the value of p_status exists in monitoring_run_status

 select count(*) into v_statusexists
 from monitoring_run_status mrs
 where mrs.status_name = p_status;
 
 if (v_statusexists = 0) THEN
   raise_application_error(-20000, 'TAGS-009: This status does not exist');
 end if;
 
 select count(*) into v_rowexists
 from monitoring_runs mr where mr.collection_name = p_collection_name and mr.run_number = p_runnumber;
 
 --when the collection + runnumber does not exist in the table, then do an insert into monitoring_runs

 if (v_rowexists = 0) THEN
 insert into monitoring_runs
   values (monrun_id.nextval, p_collection_name, p_runnumber, (select id from monitoring_run_status mrs where mrs.status_name=p_status));
   commit;
 
 --insert an "insert action" into monitoring_log

 insert into monitoring_log ml
   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);
   commit;
 
 --if collection + runnumber already exists in the table, then update monitoring_runs

 elsif (v_rowexists = 1) THEN
 
 select mr.status_id into v_oldstatus
 from monitoring_runs mr
 where mr.collection_name = p_collection_name
 and mr.run_number = p_runnumber;
 
 update monitoring_runs mr
   set status_id = (select mrs.id from monitoring_run_status mrs where mrs.status_name = p_status)
   where mr.collection_name = p_collection_name and mr.run_number = p_runnumber;
   commit;
 
 --insert an "update action" into monitoring_log

 insert into monitoring_log ml
   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);
   commit;
 
 --when v_collexists is not 0 or 1, an error must have occured --> change this to raising an exception!

 else
   raise_application_error(-20000, 'TAGS-009: Unique Key constraint violation');
 end if;
 
 end;
 /

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.