EcalCondDB/sql/dat_exists_func.sql

0001 set linesize 1000;
0002 set pagesize 50000;
0003 set feedback off;
0004 set serveroutput on size 1000000;
0005
0006 CREATE OR replace FUNCTION dat_exists (pre VARCHAR2 := NULL, iov_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   result VARCHAR2(1000);
0014   CURSOR c1 IS SELECT table_name FROM user_tables WHERE table_name LIKE like_str ORDER BY table_name ASC;
0015   t VARCHAR2(32);
0016   sql_str VARCHAR2(1000);
0017 BEGIN
0018
0019 like_str := pre || '_%_DAT';
0020 OPEN c1;
0021 LOOP
0022   FETCH c1 INTO t;
0023   EXIT WHEN c1%NOTFOUND;
0024   sql_str := 'SELECT ''1'' FROM ' || t || ' WHERE iov_id = :iov_id AND rownum = 1';
0025   OPEN exists_cur FOR sql_str USING iov_id;
0026   FETCH exists_cur INTO exists_bool;
0027   IF exists_cur%FOUND THEN
0028     result := result || ',' || t;
0029   END IF;
0030   CLOSE exists_cur;
0031 END LOOP;
0032 CLOSE c1;
0033
0034 RETURN ltrim(result,',');
0035
0036 EXCEPTION
0037 WHEN OTHERS THEN
0038 raise_application_error(-20001,'EXCEPTION - '||SQLCODE||' -ERROR- '||SQLERRM);
0039 END;
0040 /
0041 show errors;
0042 ;
0043
0044 /* Executes the exists program */
0045 SELECT iov_id, dat_exists('MON', iov_id) FROM mon_run_iov WHERE rownum < 100;
0046 SELECT iov_id, dat_exists('DCU', iov_id) FROM dcu_iov WHERE rownum < 100;