PL/SQL problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: PL/SQL problem

  1. #1
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    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?
    "High Salaries = Happiness = Project Success."

  2. #2
    Join Date
    Apr 2002
    Posts
    4
    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

  3. #3
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    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;
    "High Salaries = Happiness = Project Success."

  4. #4
    Join Date
    Apr 2002
    Posts
    4
    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;
    /



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width