EcalCondDB/sql/update_iov_dates_procedure_new.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  * GO: september 2010
0007  * This new procedure allows multiple IOVs with the same start date
0008  * IOVs have a mask based on which one can assign a given IOV to a given table
0009  *
0010  */
0011 CREATE OR REPLACE PROCEDURE update_iov_dates_new
0012 ( my_table IN VARCHAR2,
0013   my_mask IN NUMBER,
0014   start_col IN VARCHAR2,
0015   end_col IN VARCHAR2,
0016   new_start IN DATE,
0017   new_end IN OUT DATE,
0018   new_tag_id IN NUMBER ) IS
0019
0020   sql_str VARCHAR(1000);
0021   future_start DATE;
0022   dat_table_id NUMBER;
0023   last_start DATE;
0024   last_end DATE;
0025
0026   BEGIN
0027     -- Ensure IoV time has positive duration
0028     IF new_end <= new_start THEN
0029        raise_application_error(-20000, 'IOV must have ' || start_col || ' < ' || end_col);
0030     END IF;
0031
0032     -- Truncate for IoVs in the future of this one
0033     -- Fetch IOV immediately after this one
0034     sql_str := 'SELECT min(' || start_col || ') FROM ' || my_table ||
0035                ' WHERE ' || start_col || ' > :s AND tag_id = :t ' ||
0036                ' AND MASK = :i';
0037     EXECUTE IMMEDIATE sql_str INTO future_start USING new_start, new_tag_id,
0038         my_mask;
0039
0040     IF future_start IS NOT NULL THEN
0041       -- truncate this IOV
0042       new_end := future_start;
0043     END IF;
0044
0045     -- Fetch the most recent IoV prior to this one
0046     sql_str := 'SELECT max(' || start_col || ') FROM ' || my_table ||
0047                ' WHERE ' || start_col || ' <= :s AND tag_id = :t' ||
0048                ' AND MASK = :i';
0049     EXECUTE IMMEDIATE sql_str INTO last_start USING new_start, new_tag_id,
0050         my_mask;
0051
0052     IF last_start IS NULL THEN
0053         -- table has no previous data for this tag, nothing to do
0054         return;
0055     END IF;
0056
0057     -- Fetch the end of this most recent IoV
0058     sql_str := 'SELECT ' || end_col || ' FROM ' || my_table || ' WHERE ' || start_col || ' = :s AND tag_id = :t AND MASK = :i';
0059     EXECUTE IMMEDIATE sql_str INTO last_end USING last_start, new_tag_id,
0060         my_mask;
0061
0062     IF new_start = last_start THEN
0063         -- Attempted to insert overlapping IoV!
0064         raise_application_error(-20020, 'New IOV ''' || start_col || ''' overlaps older ''' || start_col || '''');
0065     ELSIF new_start < last_end THEN
0066        -- Truncate the last IoV
0067        sql_str := 'UPDATE ' || my_table || ' SET ' || end_col || ' = :new_start' ||
0068                   ' WHERE ' || start_col || ' = :last_start AND ' || end_col || ' = :last_end AND tag_id = :t AND MASK = :i';
0069        EXECUTE IMMEDIATE sql_str USING new_start, last_start, last_end, new_tag_id, my_mask;
0070     END IF;
0071   END;
0072 /
0073 show errors;