Back to home page

Project CMSSW displayed by LXR

 
 

    


File indexing completed on 2021-02-14 13:31:55

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 /