PL/SQL problem

[jlakhani]

I am having a pl/sql problem. Getting this error message:
declare
*
ERROR at line 1:
ORA-00937: not a single-group group function
ORA-06512: at line 3
ORA-06512: at line 14

pl/sql
declare
cursor cursor_rollbackmonitor_cache is
select class, count
from v\$waitstat
where class in ('free list','system undo header','system undo block',
'undo header','undo block');
any ideas?

[JP_KIRAN]

Try this...

BEGIN
FOR CUR IN (select * from v$waitstat where class in ('free list','system undo header','system undo block', 'undo header','undo block'))
LOOP
DBMS_OUTPUT.PUT_LINE(CUR.CLASS ||' : '||CUR.COUNT);
END LOOP;
END;

Hope, it helps you

[jlakhani]

hi..it worked but i am getting bunch of other errors.


systemblock_count boolean;
*
ERROR at line 9:
ORA-06550: line 9, column 2:
PLS-00103: Encountered the symbol "SYSTEMBLOCK_COUNT"
ORA-06550: line 44, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
function package pragma procedure form

========pl/sql====================
begin
for cur in (select * from v$waitstat where class in ('free list','system undo he
ader','system undo block','undo header','undo block'))
loop
dbms_output.put_line(cur.class || ';'||cur.count);
end loop;
end;
systemblock_count boolean;
monitor_cache_rec cursor_rollbackmonitor_cache%rowType;

begin
open cursor_rollbackmonitor_cache;
freelist_count := false;
systemblock_count := false;
loop
fetch cursor_rollbackmonitor_cache into monitor_cache_rec;
exit when cursor_rollbackmonitor_cache%notFound;

if cursor_rollbackmonitor_cache%Found then
if ((monitor_cache_rec.class = 'free list') AND (monitor_cache_rec.count <= 10) ) then
freelist_count := true;
end if;

if ((monitor_cache_rec.class = 'system undo block') AND (monitor_cache_rec.count <= 10) ) then
systemblock_count := true;
end if;
end if;
end loop;
close cursor_rollbackmonitor_cache;
if ( (freelist_count) OR (systemblock_count) ) then
open cursor_rollbackmonitor_cache;
loop
fetch cursor_rollbackmonitor_cache into monitor_cache_rec;
exit when cursor_rollbackmonitor_cache%notFound;

if cursor_rollbackmonitor_cache%Found then
dbms_output.put_line( ' Count for '||monitor_cache_rec.class||' = '||to_char(monitor_cache_rec.count) );
end if;
end loop;
close cursor_rollbackmonitor_cache;
end if;
end;
/
spool off;
exit;

[JP_KIRAN]

This will work for your requirement.....

DECLARE
systemblock_count boolean;
freelist_count boolean;
BEGIN
freelist_count := false;
systemblock_count := false;

FOR monitor_cache_rec IN (select * from v$waitstat where class in ('free list','system undo header','system undo block', 'undo header','undo block'))
LOOP
if ((monitor_cache_rec.class = 'free list') AND (monitor_cache_rec.count <= 10) ) then
freelist_count := true;
end if;

if ((monitor_cache_rec.class = 'system undo block') AND (monitor_cache_rec.count <= 10) ) then
systemblock_count := true;
end if;

END LOOP;

if ( (freelist_count) OR (systemblock_count) ) then
FOR monitor_cache_rec IN (select * from v$waitstat where class in ('free list','system undo header','system undo block', 'undo header','undo block'))
LOOP
dbms_output.put_line( ' Count for '||monitor_cache_rec.class||' = '||to_char(monitor_cache_rec.count) );
END LOOP;
end if;

END;
/