File indexing completed on 2024-04-06 12:23:00
0001 CREATE OR REPLACE
0002 PROCEDURE update_online_pvss_iov_date
0003 ( cndc_table IN VARCHAR2,
0004 new_since IN DATE,
0005 new_till IN DATE,
0006 logic_id IN INTEGER ) IS
0007
0008
0009
0010
0011
0012 sql_str VARCHAR(1000);
0013 future_since DATE;
0014 last_since DATE;
0015 last_till DATE;
0016
0017 BEGIN
0018
0019 IF new_till <= new_since THEN
0020 raise_application_error(-20000, 'IoV must have since < till');
0021 END IF;
0022
0023
0024 sql_str := 'SELECT max(since) FROM ' || cndc_table || ' WHERE since > :s and logic_id=:l';
0025 EXECUTE IMMEDIATE sql_str INTO future_since USING new_since,logic_id;
0026
0027 IF future_since IS NOT NULL THEN
0028 raise_application_error(-20010, 'IoVs must be inserted in increasing order: ' ||
0029 'Current highest ''since'' is ' ||
0030 to_char(future_since, 'YYYY-MM-DD HH24:MI:SS'));
0031 END IF;
0032
0033
0034 sql_str := 'SELECT max(since) FROM ' || cndc_table || ' WHERE since <= :s and logic_id=:l';
0035 EXECUTE IMMEDIATE sql_str INTO last_since USING new_since,logic_id;
0036
0037 IF last_since IS NULL THEN
0038
0039 return;
0040 END IF;
0041
0042
0043 sql_str := 'SELECT till FROM ' || cndc_table || ' WHERE since = :s and logic_id=:l';
0044 EXECUTE IMMEDIATE sql_str INTO last_till USING last_since,logic_id;
0045
0046 IF new_since = last_since THEN
0047
0048 raise_application_error(-20020, 'New IOV since overlaps older since');
0049 ELSIF new_since < last_till THEN
0050
0051 sql_str := 'UPDATE ' || cndc_table || ' SET till = :new_since WHERE since = :last_since AND till = :last_till and logic_id=:l';
0052 EXECUTE IMMEDIATE sql_str USING new_since, last_since, last_till,logic_id;
0053 END IF;
0054 END;
0055 /
0056 show errors;