Back to home page

Project CMSSW displayed by LXR

 
 

    


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

0001 set linesize 1000;
0002 set pagesize 50000;
0003 set feedback off;
0004 set serveroutput on size 1000000;
0005 
0006 CREATE OR replace FUNCTION config_exists (cycle_id NUMBER := NULL)
0007   RETURN VARCHAR2
0008 AS
0009   TYPE exists_cur_type IS REF CURSOR;
0010   exists_cur exists_cur_type;
0011   exists_bool CHAR(1);
0012   like_str VARCHAR2(100);
0013   like2_str VARCHAR2(100);
0014   result VARCHAR2(1000);
0015   CURSOR c1 IS SELECT table_name FROM user_tables WHERE (table_name LIKE like_str)  ORDER BY table_name ASC;
0016   CURSOR c2 IS SELECT table_name FROM user_tables WHERE (table_name LIKE like2_str) OR (table_name LIKE 'ECAL_SCAN_DAT') ORDER BY table_name ASC;
0017   t VARCHAR2(32);
0018   t2 VARCHAR2(32);
0019   t3 VARCHAR2(32);
0020   sql_str VARCHAR2(1000);
0021 BEGIN
0022 
0023 like_str := 'ECAL_%_CYCLE';
0024 like2_str := 'ECAL_%_CONFIGURATION';
0025 OPEN c1;
0026 OPEN c2;
0027 LOOP
0028   FETCH c2 INTO t2;
0029   FETCH c1 INTO t;
0030   EXIT WHEN c1%NOTFOUND;
0031   sql_str := 'SELECT ''1'' FROM ' || t || ' WHERE cycle_id = :cycle_id AND rownum = 1';
0032   OPEN exists_cur FOR sql_str USING cycle_id;
0033   FETCH exists_cur INTO exists_bool;
0034   IF exists_cur%FOUND THEN
0035         t3 := t2;
0036         if (t2  LIKE 'ECAL_SCAN_DAT' ) THEN
0037            t3 :='ECAL_SCAN_CONFIGURATION' ; 
0038         END IF;
0039     result := result || ',' || t3;
0040   END IF;
0041   CLOSE exists_cur;
0042 END LOOP;
0043 CLOSE c1;
0044 CLOSE c2;
0045 
0046 RETURN ltrim(result,',');
0047 
0048 EXCEPTION
0049 WHEN OTHERS THEN
0050 raise_application_error(-20001,'EXCEPTION - '||SQLCODE||' -ERROR- '||SQLERRM);
0051 END;
0052 /
0053 show errors;
0054 ;