File indexing completed on 2024-04-06 12:23:00
0001
0002
0003
0004
0005
0006
0007
0008
0009
0010
0011
0012
0013
0014
0015
0016
0017
0018
0019
0020 WHENEVER SQLERROR EXIT
0021
0022 CREATE OR REPLACE PROCEDURE TESTDB IS
0023 gname VARCHAR2(64);
0024 BEGIN
0025 SELECT GLOBAL_NAME INTO gname FROM GLOBAL_NAME;
0026 IF gname = 'INT2R.CERN.CH' THEN
0027
0028 return;
0029 ELSE
0030 RAISE_APPLICATION_ERROR(-20001, 'Wrong DB name: ' || gname);
0031 END IF;
0032 END;
0033 /
0034
0035 EXEC TESTDB;
0036
0037
0038
0039 CREATE OR REPLACE FUNCTION bitnot (x IN NUMBER) RETURN NUMBER AS
0040 BEGIN
0041 return (-1-x);
0042 END;
0043 /
0044
0045 CREATE OR REPLACE PROCEDURE update_iov_dates_test
0046 ( my_table IN VARCHAR2,
0047 my_mask IN NUMBER,
0048 my_sequence IN VARCHAR2,
0049 my_dattable IN VARCHAR2,
0050 start_col IN VARCHAR2,
0051 end_col IN VARCHAR2,
0052 new_start IN DATE,
0053 new_end IN OUT DATE,
0054 new_tag_id IN NUMBER ) IS
0055 sql_str VARCHAR(1000);
0056 tn VARCHAR(25);
0057 tnc NUMBER;
0058 last_start DATE;
0059 last_iov NUMBER;
0060 new_iov NUMBER;
0061 zero_iov NUMBER;
0062 last_mask NUMBER;
0063 new_mask NUMBER;
0064 my_rows NUMBER;
0065 i NUMBER;
0066 j NUMBER;
0067
0068 BEGIN
0069 dbms_output.enable(100000);
0070
0071 IF new_end <= new_start THEN
0072 raise_application_error(-20000, 'IOV must have ' || start_col || ' < '
0073 || end_col);
0074 END IF;
0075
0076 sql_str := 'SELECT COUNT(IOV_ID) FROM ' || my_table ||
0077 ' WHERE BITAND(MASK, :my_mask) > 0 AND TAG_ID = :t AND ' || end_col ||
0078 ' >= TO_DATE(''31-12-9999 23:59:59'', ''DD-MM-YYYY HH24:MI:SS'')';
0079 EXECUTE IMMEDIATE sql_str INTO my_rows USING my_mask, new_tag_id;
0080 IF my_mask != 0 THEN
0081 dbms_output.put_line('----------------------------------------');
0082 END IF;
0083 dbms_output.put_line(sql_str);
0084 dbms_output.put_line('Searching for mask ' || my_mask || ' tag ' || new_tag_id);
0085 dbms_output.put_line('Found ' || my_rows || ' rows with good mask');
0086
0087 IF my_rows = 0 THEN
0088
0089 dbms_output.put_line('Inserting row with mask ' || my_mask || ' and tag ' || new_tag_id);
0090 return;
0091 ELSE
0092
0093 FOR i IN 1..my_rows LOOP
0094
0095 sql_str := 'SELECT IOV_ID, MASK, SINCE FROM ' || my_table ||
0096 ' WHERE BITAND(MASK, ' ||
0097 my_mask || ') > 0 AND TAG_ID = :t AND ' || end_col ||
0098 ' >= TO_DATE(''31-12-9999 23:59:59'', ''DD-MM-YYYY HH24:MI:SS'')' ||
0099 ' AND ROWNUM = 1';
0100 dbms_output.put_line(sql_str);
0101 EXECUTE IMMEDIATE sql_str INTO last_iov, last_mask, last_start USING new_tag_id;
0102 dbms_output.put_line('Required insertion of data with mask: ' || my_mask);
0103 dbms_output.put_line('Found data with mask : ' || last_mask
0104 || ' and IOV ' || last_iov);
0105 dbms_output.put_line(' and start date on ' || last_start);
0106
0107 new_mask := BITAND(last_mask, BITNOT(my_mask));
0108 IF new_mask > 0 THEN
0109 sql_str := 'UPDATE ' || my_table || ' SET MASK = BITAND(' ||
0110 ':last_mask, BITNOT(:my_mask)) WHERE IOV_ID = :last_iov AND BITAND( '
0111 || ':last_mask , BITNOT(:my_mask)) > 0';
0112 dbms_output.put_line(sql_str);
0113 EXECUTE IMMEDIATE sql_str USING last_mask, my_mask, last_iov, last_mask,
0114 my_mask;
0115
0116
0117 last_mask := BITAND(last_mask, my_mask);
0118 sql_str := 'INSERT INTO ' || my_table || ' VALUES (' ||
0119 my_sequence || '.NextVal, 0, :tag_id, :since, :till)';
0120 dbms_output.put_line('INSERTing new record with mask ' || last_mask ||
0121 ' and since, till = ' || last_start || ', ' || new_start);
0122 EXECUTE IMMEDIATE sql_str USING new_tag_id, last_start, new_start;
0123
0124 sql_str := 'SELECT IOV_ID FROM ' || my_table ||
0125 ' WHERE MASK = 0';
0126 EXECUTE IMMEDIATE sql_str INTO zero_iov;
0127 dbms_output.put_line('Found IOV_ID = ' || zero_iov ||
0128 ' with mask = 0');
0129
0130 j := 1;
0131 WHILE j <= my_mask LOOP
0132
0133 sql_str := 'SELECT COUNT(TABLE_NAME) FROM ' || my_dattable ||
0134 ' WHERE BITAND(BITAND(ID, :j), :my_mask) > 0';
0135 EXECUTE IMMEDIATE sql_str INTO tnc USING j, my_mask;
0136 IF tnc > 0 THEN
0137 sql_str := 'SELECT TABLE_NAME FROM ' ||
0138 my_dattable ||
0139 ' WHERE BITAND(BITAND(ID, :j), :my_mask) > 0';
0140 EXECUTE IMMEDIATE sql_str INTO tn USING j, my_mask;
0141 dbms_output.put_line('Found table ' || tn ||
0142 ' to be updated');
0143 dbms_output.put_line(' Setting IOV_ID = ' ||
0144 zero_iov || ' from ' || last_iov);
0145 sql_str := 'UPDATE ' || tn || ' SET IOV_ID = :zero_iov WHERE IOV_ID = '
0146 || ':last_iov';
0147 EXECUTE IMMEDIATE sql_str USING zero_iov, last_iov;
0148 dbms_output.put_line(sql_str);
0149 END IF;
0150 j := j * 2;
0151 END LOOP;
0152
0153 sql_str := 'UPDATE ' || my_table || ' SET MASK = :my_mask WHERE MASK = 0';
0154 EXECUTE IMMEDIATE sql_str USING my_mask;
0155 dbms_output.put_line(sql_str || ' using ' || my_mask);
0156 ELSE
0157
0158 sql_str := 'UPDATE ' || my_table || ' SET TILL = :new_start ' ||
0159 ' WHERE IOV_ID = :last_iov';
0160 EXECUTE IMMEDIATE sql_str USING new_start, last_iov;
0161 dbms_output.put_line('Updated IOV ' || last_iov || ' Set TILL = ' ||
0162 new_start);
0163 END IF;
0164 END LOOP;
0165 END IF;
0166 END;
0167 /
0168
0169 show errors;
0170
0171 SET ECHO OFF
0172 SET HEADING OFF
0173 SPOOL tmp.sql
0174 SELECT 'DROP TABLE ' || TABLE_NAME || ';' FROM USER_TABLES WHERE
0175 TABLE_NAME LIKE 'TEST%';
0176 SELECT 'DROP SEQUENCE ' || SEQUENCE_NAME || ';' FROM USER_SEQUENCES WHERE
0177 SEQUENCE_NAME LIKE 'TEST%';
0178 SPOOL OFF
0179 @tmp;
0180
0181 SET HEADING ON
0182
0183 CREATE TABLE TEST_IOV (
0184 IOV_ID NUMBER,
0185 MASK NUMBER,
0186 TAG_ID NUMBER,
0187 SINCE DATE,
0188 TILL DATE
0189 );
0190
0191 CREATE TABLE TEST_DATTABLE_ID (
0192 ID NUMBER,
0193 TABLE_NAME VARCHAR2(25),
0194 DESCRIPTION VARCHAR2(255)
0195 );
0196
0197 INSERT INTO TEST_DATTABLE_ID VALUES (1, 'TEST_A', '');
0198 INSERT INTO TEST_DATTABLE_ID VALUES (2, 'TEST_B', '');
0199 INSERT INTO TEST_DATTABLE_ID VALUES (4, 'TEST_C', '');
0200
0201 CREATE TABLE TEST_A (
0202 IOV_ID NUMBER,
0203 DATA NUMBER
0204 );
0205
0206 CREATE TABLE TEST_B (
0207 IOV_ID NUMBER,
0208 DATA NUMBER
0209 );
0210
0211 CREATE TABLE TEST_C (
0212 IOV_ID NUMBER,
0213 DATA NUMBER
0214 );
0215
0216 CREATE OR REPLACE TRIGGER TEST_IOV_TG
0217 BEFORE INSERT ON TEST_IOV
0218 REFERENCING NEW AS newiov
0219 FOR EACH ROW
0220 CALL update_iov_dates_test('TEST_IOV', :newiov.mask,
0221 'TEST_IOV_SQ', 'TEST_DATTABLE_ID', 'SINCE', 'TILL',
0222 :newiov.since,
0223 :newiov.till, :newiov.tag_id)
0224 /
0225
0226 show errors;
0227
0228 alter session set NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS';
0229 set linesize 110
0230 SET SERVEROUTPUT ON
0231
0232
0233
0234
0235
0236
0237
0238 CREATE SEQUENCE TEST_IOV_SQ START WITH 1 NOCACHE;
0239 insert into test_iov values(TEST_IOV_SQ.NextVal, 1, 1,
0240 to_date('01-01-2010 00:00:00', 'DD-MM-YYYY HH24:MI:SS'),
0241 to_date('31-12-9999 23:59:59', 'DD-MM-YYYY HH24:MI:SS'));
0242 INSERT INTO TEST_A VALUES (1, 1);
0243 insert into test_iov values(TEST_IOV_SQ.NextVal, 1, 1,
0244 to_date('02-01-2010 00:00:00', 'DD-MM-YYYY HH24:MI:SS'),
0245 to_date('31-12-9999 23:59:59', 'DD-MM-YYYY HH24:MI:SS'));
0246 INSERT INTO TEST_A VALUES (2, 2);
0247 insert into test_iov values(TEST_IOV_SQ.NextVal, 1, 1,
0248 to_date('03-01-2010 00:00:00', 'DD-MM-YYYY HH24:MI:SS'),
0249 to_date('31-12-9999 23:59:59', 'DD-MM-YYYY HH24:MI:SS'));
0250 INSERT INTO TEST_A VALUES (3, 3);
0251 insert into test_iov values(TEST_IOV_SQ.NextVal, 3, 1,
0252 to_date('04-01-2010 00:00:00', 'DD-MM-YYYY HH24:MI:SS'),
0253 to_date('31-12-9999 23:59:59', 'DD-MM-YYYY HH24:MI:SS'));
0254 INSERT INTO TEST_A VALUES (4, 4);
0255 INSERT INTO TEST_B VALUES (4, 1);
0256 insert into test_iov values(TEST_IOV_SQ.NextVal, 4, 1,
0257 to_date('05-01-2010 00:00:00', 'DD-MM-YYYY HH24:MI:SS'),
0258 to_date('31-12-9999 23:59:59', 'DD-MM-YYYY HH24:MI:SS'));
0259 INSERT INTO TEST_C VALUES (5, 1);
0260 insert into test_iov values(TEST_IOV_SQ.NextVal, 3, 1,
0261 to_date('06-01-2010 00:00:00', 'DD-MM-YYYY HH24:MI:SS'),
0262 to_date('31-12-9999 23:59:59', 'DD-MM-YYYY HH24:MI:SS'));
0263 INSERT INTO TEST_A VALUES (6, 5);
0264 INSERT INTO TEST_B VALUES (7, 2);
0265 insert into test_iov values(TEST_IOV_SQ.NextVal, 2, 1,
0266 to_date('07-01-2010 00:00:00', 'DD-MM-YYYY HH24:MI:SS'),
0267 to_date('31-12-9999 23:59:59', 'DD-MM-YYYY HH24:MI:SS'));
0268 INSERT INTO TEST_B VALUES (8, 3);
0269
0270 SELECT * FROM TEST_IOV;
0271 SELECT * FROM TEST_IOV JOIN TEST_A ON TEST_IOV.IOV_ID = TEST_A.IOV_ID
0272 WHERE BITAND(MASK, 1) = 1 ORDER BY SINCE ASC;
0273 SELECT * FROM TEST_IOV JOIN TEST_B ON TEST_IOV.IOV_ID = TEST_B.IOV_ID
0274 WHERE BITAND(MASK, 2) = 2 ORDER BY SINCE ASC;
0275 SELECT * FROM TEST_IOV JOIN TEST_C ON TEST_IOV.IOV_ID = TEST_C.IOV_ID
0276 WHERE BITAND(MASK, 4) = 4 ORDER BY SINCE ASC;