File indexing completed on 2024-04-06 12:22:58
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 ;
