EcalCondDB/sql/update_odsubrun_iov_dates.sql

0001 /*
0002  *
0003  * Procedure to validate an subrun IoV to be inserted and update a previous
0004  * IoV so that there are no overlaps.
0005  * The run_iov is essentially another tag for a subrun-type IoV table
0006  */
0007 CREATE OR REPLACE PROCEDURE update_odsubrun_iov_dates
0008 ( my_table IN VARCHAR2,
0009   start_col IN VARCHAR2,
0010   end_col IN VARCHAR2,
0011   new_start IN DATE,
0012   new_end IN OUT DATE,
0013   new_run_iov_id IN NUMBER ) IS
0014
0015   sql_str VARCHAR(1000);
0016   future_start DATE;
0017   last_start DATE;
0018   last_end DATE;
0019
0020   BEGIN
0021     -- Ensure IoV time has positive duration
0022     IF new_end <= new_start THEN
0023        raise_application_error(-20000, 'IoV must have ' || start_col || ' < ' || end_col);
0024     END IF;
0025
0026     -- Truncate for IoVs in the future of this one
0027     -- Fetch IOV immediately after this one
0028     sql_str := 'SELECT min(' || start_col || ') FROM ' || my_table ||
0029                ' WHERE ' || start_col || ' > :s  AND run_iov_id = :r';
0030     EXECUTE IMMEDIATE sql_str INTO future_start USING new_start,  new_run_iov_id;
0031
0032     IF future_start IS NOT NULL THEN
0033       -- truncate this IOV
0034       new_end := future_start;
0035     END IF;
0036
0037     -- Fetch the most recent IoV prior to this one
0038     sql_str := 'SELECT max(' || start_col || ') FROM ' || my_table ||
0039                ' WHERE ' || start_col || ' <= :s AND  run_iov_id = :r';
0040     EXECUTE IMMEDIATE sql_str INTO last_start USING new_start, new_run_iov_id;
0041
0042     IF last_start IS NULL THEN
0043         -- table has no previous data for this tag, nothing to do
0044         return;
0045     END IF;
0046
0047     -- Fetch the end of this most recent IoV
0048     sql_str := 'SELECT ' || end_col || ' FROM ' || my_table || ' WHERE ' || start_col || ' = :s AND run_iov_id = :r';
0049     EXECUTE IMMEDIATE sql_str INTO last_end USING last_start, new_run_iov_id;
0050
0051     IF new_start = last_start THEN
0052         -- Attempted to insert overlapping IoV!
0053         raise_application_error(-20020, 'New IOV ''' || start_col || ''' overlaps older ''' || start_col || '''');
0054     ELSIF new_start < last_end THEN
0055        -- Truncate the last IoV
0056        sql_str := 'UPDATE ' || my_table || ' SET ' || end_col || ' = :new_start' ||
0057                   ' WHERE ' || start_col || ' = :last_start AND ' || end_col || ' = :last_end AND run_iov_id = :r';
0058        EXECUTE IMMEDIATE sql_str USING new_start, last_start, last_end, new_run_iov_id;
0059     END IF;
0060   END;
0061 /
0062 show errors;
0063
0064