Query to find level information
DECLARE
l_sql VARCHAR2(1000);
l_num NUMBER;
l_dbschema VARCHAR2(240) := 'DM12';
l_query VARCHAR2(2000);
l_gtable VARCHAR2(50);
l_status VARCHAR2(50);
l_table_label VARCHAR2(100);
TYPE rcur IS REF CURSOR;
rec rcur;
BEGIN
l_query := 'SELECT gtable,table_label,status FROM '||l_dbschema||'.group_tables
WHERE 1 = 1
AND UPPER(search_table) IN (''ITEMS'',''LOCATION'')';
OPEN rec FOR l_query;
LOOP
FETCH rec INTO l_gtable,l_table_label,l_status;
EXIT WHEN rec%NOTFOUND;
l_sql := null;
l_num := 0;
l_sql := 'SELECT count(*) FROM '||l_dbschema||'.'||l_gtable;
EXECUTE IMMEDIATE l_sql INTO l_num;
IF l_num > 2 THEN
dbms_output.put_line('Level Name :'||l_table_label||' Table : '||l_gtable||' Status: '||l_status);
END IF;
END LOOP;
CLOSE rec;
END;
