Demantra- Query to find level information

 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;