EcalCondDB/sql/update_iov_dates_procedure.sql

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