Results 1 to 6 of 6

Thread: Cursor issues

  1. #1
    Join Date
    Aug 2005
    Posts
    40

    Unanswered: Cursor issues

    This procedure runs fine until u get to the cursor at the bottom. Also,
    there seems to be a problem with the select statment on line 1. It keeps saying it is looking for a terminator.

    CREATE OR REPLACE PROCEDURE ONEDAY_d(DDD in DATE) AUTHID CURRENT_USER is

    BEGIN

    DECLARE

    SLOW VARCHAR2(1500);

    SLOW2 VARCHAR2(500);

    THRESHOLD NUMBER;



    TYPE TFATG IS RECORD(

    TABLE_NAME VARCHAR2(255);

    ANALYZED_SIZE NUMBER;

    CURRENT_SIZE NUMBER;

    CHANGE_PERC VARCHAR2(5);

    LAST_ANALYZED DATE);

    TYPE CFATG IS REF CURSOR;

    FATG TFATG ;

    BEGIN



    SLOW := 'select /*+ ordered */ u.name||'.'||'o.name table_name,'
    ||'1 + s.groups + t.blkcnt + t.empcnt analyzed_size, s.blocks current_size,'

    ||'substr(to_char(100 * (s.blocks - 1 - s.groups - t.blkcnt - t.empcnt) /'||'(1 + s.groups + t.blkcnt + t.empcnt), ''9999.00''), 2)||''%'' change,'

    ||'to_char(t.analyzetime,''MM-DD-YYYY'') last_analyzed'||'from sys.file$ f, '||'sys.seg$ s, '||'sys.tab$ t, '||'sys.obj$ o, '||'sys.user$ u'

    ||'where s.file# = f.file# and'||'s.type# = 5 and'||'t.ts# = s.ts# and'||'t.file# = s.file# and'||'t.block# = s.block# and'

    ||'abs(s.blocks - 1 - s.groups - t.blkcnt - t.empcnt) > 4 and'||'o.obj# = t.obj# and'||'u.user# = o.owner#'

    ||'union all'||'select /*+ ordered */ u.name||'.'||o.name||':'||o.subname table_name,'||'1 + s.groups + t.blkcnt + t.empcnt analyzed_size, s.blocks current_size,'

    ||'substr(to_char(100 * (s.blocks - 1 - s.groups - t.blkcnt - t.empcnt) /'||'(1 + s.groups + t.blkcnt + t.empcnt), ''9999.00''), 2)||''%'' change,'

    ||'to_char(t.analyzetime,''MM-DD-YYYY'') last_analyzed||'from sys.file$ f,

    ||'sys.seg$ s,

    ||'sys.tabpart$ t,

    ||'sys.obj$ o,

    ||'sys.user$ u

    ||'where s.file# = f.file# and

    ||'s.type# = 5 and

    ||'t.ts# = s.ts# and

    ||'t.file# = s.file# and

    ||'t.block# = s.block# and

    ||'abs(s.blocks - 1 - s.groups - t.blkcnt - t.empcnt) > 4 and

    ||'o.obj# = t.obj# and

    ||'u.user# = o.owner#

    ||'order by 4';







    OPEN CFATG FOR SLOW;
    LOOP

    FETCH CFATG INTO FATG;
    EXIT WHEN CFATG%NOTFOUND;

    IF FATG.CHANGE_PERC > 5% or < -5%
    and FATG.TABLE_NAME NOT LIKE ('FDB')

    then SLOW2 := exec DBMS_STATS.GATHER_TABLE_STATS('|| FATG.TABLE_NAME||','||dbms_stats.default_degree||, ||cascade => TRUE||);'
    from DBA_TABLES where owner not in ('SYSTEM','SYS','FDB');

    EXECUTE IMMEDIATE (SLOW2 );
    END LOOP;
    END;

    END;

    /

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'd say you should count your single quotation marks more carefully.

  3. #3
    Join Date
    Aug 2005
    Posts
    40
    ok, i fixed the single quotation marks but the cursor still will not work.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Try printing the query string before EXECUTEing it - sometimes it makes debugging a bit easier.

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Your code at the end doesnt make sense i.e. where is the end if.

    Also why are you doing slow2:= exec ... from DBA TABLES???? surely its much easier to just call dbms_stats without using dynamic sql?

    Alan

  6. #6
    Join Date
    Aug 2005
    Posts
    40
    that is what my boss wanted to use. He is so scared of oracle packages.
    I believe is is just not being educated.

Posting Permissions

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