Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2005
    Posts
    40

    Unanswered: pls-006502 error

    Ok, i changed my procedure and it is now valid.
    However, when I run it in sqlplus I get the following error on line 58 which is the fetch statement:


    PL/SQL: numeric or value error: character to number conversion error

    CREATE OR REPLACE PROCEDURE CHKS AUTHID CURRENT_USER is
    BEGIN
    DECLARE
    V_TABLE_NAME VARCHAR2(255);
    V_ANALYZED_SIZE NUMBER;
    V_CURRENT_SIZE NUMBER;
    V_CHANGE_PERC NUMBER;
    V_LAST_ANALYZED DATE;

    SSQL2 VARCHAR2(500);


    CURSOR CSTIS
    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;

    BEGIN

    OPEN CSTATREC;

    LOOP
    FETCH CST INTO V_TABLE_NAME, V_ANALYZED_SIZE,V_CURRENT_SIZE,V_CHANGE_PERC,V_LAS T_ANALYZED;
    EXIT WHEN CSTATREC%NOTFOUND;

    IF V_CHANGE_PERC > .5
    OR V_CHANGE_PERC < .5
    AND V_TABLE_NAME LIKE ('AUDIT')

    then
    BEGIN
    SSQL2 := 'DBMS_STATS.GATHER_TABLE_STATS('||V_TABLE_NAME||') ';
    DBMS_OUTPUT.PUT_LINE(SSQL2);
    EXECUTE IMMEDIATE SSQL2;

    END;
    END IF;
    END LOOP;
    END;
    END;
    /

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I didn't check your procedure too carefully, but - if you say it is valid - I'll accept it (if "valid" means that it compiled successfully). However, there are BEGIN-ENDs that could be dropped out. They can be used the way you do, but it is not necessary and - having the code unstructured as yours is - makes the procedure ugly by the means of debugging and understanding by third party (read: me).

    Now, back to your problem: why do you think this will work?
    1. you declared a cursor named CSTIS
    2. you opened a cursor named CSTATREC
    3. you fetched cursor named CST into several variables

    Three commands, three cursors. I guess you wanted to deal with only one cursor.

    Furthermore: ORA-06502 is
    Code:
    ORA-06502 PL/SQL: numeric or value error string
    
    Cause: An arithmetic, numeric, string, conversion, or constraint error
    occurred. For example, this error occurs if an attempt is made to assign the 
    value NULL to a variable declared NOT NULL, or if an attempt is made to 
    assign an integer larger than 99 to a variable declared NUMBER(2).
    
    Action: Change the data, how it is manipulated, or how it is declared so that 
    values do not violate constraints
    First variable is a VARCHAR2(255); is it large enough? The other four variables are declared as numbers; I guess they will be able to accept cursor values (once you start to use it properly).

  3. #3
    Join Date
    Nov 2002
    Posts
    272
    It looks like you try to fetch a non-numeric value into a number variable. In your cursor, the fourth value is substr(something) || '%'. My guess is the '%' makes it a non-numeric value.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    So true, Ivon ... I didn't notice that.

    Value that is to be stored into the column is something like "35.29%" - why are you storing % sign into the column? It would be better to store just a number (35.29) and format it (including the % sign) later in report.

  5. #5
    Join Date
    Aug 2005
    Posts
    40
    Thank you so much for your replies. I finally got it working. Now I am faced with another problem. This procedure is valid and it works however it will still analyze all of the schemas that I have in my NOT IN statement. Why is that?
    I am using NOT IN because I want it to analyze only the schemas that are not in the the NOT IN statement.

    CREATE OR REPLACE PROCEDURE ORACLE.CHECKTABLESTATS2 AUTHID CURRENT_USER IS
    BEGIN
    DECLARE
    V_SCHEMA_NAME VARCHAR2(255);
    V_TABLE_NAME VARCHAR2(255);
    V_SUB_NAME VARCHAR2(255);
    V_ANALYZED_SIZE NUMBER;
    V_CURRENT_SIZE NUMBER;
    V_CHANGE_PERC NUMBER;
    V_LAST_ANALYZED DATE;




    CURSOR C1 IS
    SELECT /*+ ordered */ u.name schema_name, o.name table_name, o.subname sub_name,
    1 + s.groups + t.blkcnt + t.empcnt analyzed_size, s.blocks current_size,
    100 * (s.blocks - 1 - s.groups - t.blkcnt - t.empcnt) /
    (1 + s.groups + t.blkcnt + t.empcnt) change,
    t.analyzetime 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 schema_name, o.name table_name, o.subname sub_name,
    1 + s.groups + t.blkcnt + t.empcnt analyzed_size, s.blocks current_size,
    100 * (s.blocks - 1 - s.groups - t.blkcnt - t.empcnt) /
    (1 + s.groups + t.blkcnt + t.empcnt) change,
    t.analyzetime 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;

    BEGIN


    OPEN C1;



    LOOP
    FETCH C1 INTO V_SCHEMA_NAME, V_TABLE_NAME, V_SUB_NAME,V_ANALYZED_SIZE,V_CURRENT_SIZE,V_CHANGE _PERC,V_LAST_ANALYZED;
    EXIT WHEN C1%NOTFOUND;

    IF V_CHANGE_PERC > 5
    OR V_CHANGE_PERC < 5
    AND V_TABLE_NAME NOT IN ('CDRDB')
    AND V_SCHEMA_NAME NOT IN ('SYS', 'SYSTEM','WMSYS','OUTLN','DFC', 'TFC')


    THEN

    IF V_SUB_NAME is not null then
    DBMS_OUTPUT.PUT_LINE (to_char(sysdate,'mm/dd/yyyy, HH:MIam') ||','||(V_TABLE_NAME));
    dbms_stats.gather_table_stats(ownname=> V_SCHEMA_NAME, tabname=> V_TABLE_NAME, partname=> V_SUB_NAME, estimate_percent=> 10 );
    ELSE
    dbms_stats.gather_table_stats(ownname=> V_SCHEMA_NAME, tabname=> V_TABLE_NAME, partname=> null, estimate_percent=> 10 );
    END IF;
    END IF;
    END LOOP;
    END;
    END;
    /

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Would it help if you include list of schemas into the cursor declaration instead of in the IF-THEN-ELSE statement? Such as

    CURSOR c1 IS SELECT ...
    FROM sys.user$ u, ...
    WHERE u.name NOT IN ('SYS', 'SYSTEM', ...)

    And, what's the purpose of this:

    IF v_change_perc > 5 OR v_change_perc < 5 THEN ...?

Posting Permissions

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