File indexing completed on 2024-04-06 12:22:58
0001 set serveroutput on size 100000 format wrapped;
0002
0003 DECLARE
0004
0005 TYPE beams_ary IS VARRAY (2) OF VARCHAR2(2);
0006 beams beams_ary;
0007 beam VARCHAR2(2);
0008 create_sql VARCHAR2(10000);
0009 tbl VARCHAR2(32);
0010 var VARCHAR2(32);
0011 field VARCHAR2(255);
0012
0013 BEGIN
0014 beams := beams_ary('H4', 'H2');
0015
0016 FOR i IN 1..2
0017 LOOP
0018 beam := beams(i);
0019 tbl := 'RUN_' || beam || '_BEAM_DAT';
0020 create_sql := 'CREATE TABLE ' || tbl || ' (iov_id NUMBER(10), ';
0021 dbms_output.put_line(create_sql);
0022
0023 FOR result IN (SELECT variable_name, datatype FROM beam_source WHERE beamline=beam ORDER BY variable_name)
0024 LOOP
0025 var := result.variable_name;
0026
0027 IF result.datatype = 'NUMERIC' THEN
0028 field := '"' || var || '"' || ' NUMBER';
0029 ELSIF result.datatype = 'TEXTUAL' THEN
0030 field := '"' || var || '"' || ' VARCHAR2(100)';
0031 ELSE raise_application_error(-20000, 'UNKNOWN DATATYPE');
0032 END IF;
0033
0034 dbms_output.put_line(field);
0035 create_sql := create_sql || field || ',';
0036 END LOOP;
0037
0038 create_sql := trim(TRAILING ',' FROM create_sql) || ')';
0039
0040 EXECUTE IMMEDIATE create_sql;
0041 EXECUTE IMMEDIATE 'ALTER TABLE ' || tbl || ' ADD CONSTRAINT '
0042 || tbl || '_fk FOREIGN KEY (iov_id) REFERENCES run_iov (iov_id)';
0043 END LOOP;
0044 END;
0045 /
