EcalCondDB/sql/update_online_pvss_iov_number_proc.sql

0001 CREATE OR REPLACE
0002 PROCEDURE update_online_pvss_iov_number
0003 ( cndc_table IN VARCHAR2,
0004   new_since IN NUMBER,
0005   new_till IN NUMBER,
0006   logic_id IN INTEGER ) IS
0007 /*

0008  *

0009  * Procedure to validate an IoV to be inserted and update a previous

0010  * IoV so that there are no overlaps.

0011  * This is just a copy of update_online_pvss_iov_date but with the times represented as numbers (e.g. of microseconds since 1970) instead of timestamps

0012  */
0013   sql_str VARCHAR(1000);
0014   future_since NUMBER;
0015   last_since NUMBER;
0016   last_till NUMBER;
0017
0018   BEGIN
0019     -- Ensure IoV has positive duration

0020     IF new_till <= new_since THEN
0021        raise_application_error(-20000, 'IoV must have since < till');
0022     END IF;
0023
0024     -- Make sure that there are no IoVs in the future of this one

0025     sql_str := 'SELECT max(since) FROM ' || cndc_table || ' WHERE since > :s and logic_id=:l';
0026     EXECUTE IMMEDIATE sql_str INTO future_since USING new_since,logic_id;
0027
0028     IF future_since IS NOT NULL THEN
0029       raise_application_error(-20010, 'IoVs must be inserted in increasing order:  ' ||
0030                                       'Current highest ''since'' is ' ||
0031                                       to_char(future_since));
0032     END IF;
0033
0034     -- Fetch the most recent IoV prior to this one

0035     sql_str := 'SELECT max(since) FROM ' || cndc_table || ' WHERE since <= :s and logic_id=:l';
0036     EXECUTE IMMEDIATE sql_str INTO last_since USING new_since,logic_id;
0037
0038     IF last_since IS NULL THEN
0039         -- table has no data, nothing to do

0040         return;
0041     END IF;
0042
0043     -- Fetch the till of this most recent IoV

0044     sql_str := 'SELECT till FROM ' || cndc_table || ' WHERE since = :s and logic_id=:l';
0045     EXECUTE IMMEDIATE sql_str INTO last_till USING last_since,logic_id;
0046
0047     IF new_since = last_since THEN
0048         -- Attempted to insert overlapping IoV!

0049         raise_application_error(-20020, 'New IOV since overlaps older since');
0050     ELSIF new_since < last_till THEN
0051        -- Truncate the last IoV

0052        sql_str := 'UPDATE ' || cndc_table || ' SET till = :new_since WHERE since = :last_since AND till = :last_till and logic_id=:l';
0053        EXECUTE IMMEDIATE sql_str USING new_since, last_since, last_till,logic_id;
0054     END IF;
0055   END;
0056 /
0057 show errors;