Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    8

    Unanswered: ORA-06502: PL/SQL: numeric or value error

    Hi All,
    I am on the situation that when I called a procedure as the following:

    ---Code from here
    PROCEDURE FPTransformAlpgrToGroup(v_group_start IN PLS_INTEGER) IS
    v_group_num PLS_INTEGER;
    v_group_num_tmp PLS_INTEGER;
    v_group_num_base PLS_INTEGER;
    v_group_total PLS_INTEGER;
    v_max_stuee PLS_INTEGER;
    v_alpgr_count PLS_INTEGER;
    v_son_index PLS_INTEGER;
    v_loop_count PLS_INTEGER;

    TYPE TABLEOFINT IS TABLE OF INT;
    v_alpgr_count_list TABLEOFINT;
    BEGIN
    v_g_operation := 'TransformAlpgrToGroup';

    .... Some code here

    EXCEPTION
    WHEN OTHERS THEN
    WriteLog ('IDB_BR2_BOM.FPTransformAlpgrToGroup', v_g_operation, SQLCODE, SUBSTR(SQLERRM, 1, 512), 'E', NULL);
    COMMIT;
    END FPTransformAlpgrToGroup;

    ----Code End

    the problem is that this procedure is called in a package in a loop,sometimes it will araise the "ORA-06502: PL/SQL: numeric or value error",the jumps to WriteLog function and write a log for waring.
    but I check the code for any varible type,just could't locate what's wrong with it.PS: the calling code is
    --->FPTransformAlpgrToGroup(100).
    Any pearls of wisdom will be most welcome.
    Rgs,
    xujb

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    i can not see any code that will give the error. But maybe it is in the code you did not provide.
    Do you know for sure that the call to the procedure is always with 100 or might it be called with another value ?
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Sep 2003
    Posts
    8

    Hi,evanhattem

    Originally posted by evanhattem
    hi,

    i can not see any code that will give the error. But maybe it is in the code you did not provide.
    Do you know for sure that the call to the procedure is always with 100 or might it be called with another value ?
    ------------------------------------------
    I will provide the complete code of procedure,and I am sure the call to the procedure is always with 100,since it is called from another package
    ,some code of package are--->
    IF (v_l_FP_not_deal = 0)
    THEN
    FPTransformAlpgrToGroup(100);
    FPCompressBOMLevel(100);
    FPTransformBOMTree;
    END IF;

    the procedure code:
    -------------------------Code Begins here
    PROCEDURE FPTransformAlpgrToGroup(v_group_start IN PLS_INTEGER) IS
    v_group_num PLS_INTEGER;
    v_group_num_tmp PLS_INTEGER;
    v_group_num_base PLS_INTEGER;
    v_group_total PLS_INTEGER;
    v_max_stuee PLS_INTEGER;
    v_alpgr_count PLS_INTEGER;
    v_son_index PLS_INTEGER;
    v_loop_count PLS_INTEGER;

    TYPE TABLEOFINT IS TABLE OF INT;
    v_alpgr_count_list TABLEOFINT;
    BEGIN
    --v_g_operation := 'TransformAlpgrToGroup';
    v_g_operation := 'TransformAlpgrToGroupStart1';
    v_group_num := v_group_start;
    v_g_operation := 'TransformAlpgrToGroupStart2';
    SELECT MAX(stuee) INTO v_max_stuee
    FROM MD_BOM_ORG_T1;
    v_g_operation := 'TransformAlpgrToGroupEnd';
    -- Transform substitute item groups to substitute groups
    FOR v_current_level IN 2 .. v_max_stuee
    LOOP
    v_g_operation := 'TransformAlpgrToGroupLoop1';
    FOR father_rec IN (SELECT DISTINCT id
    FROM MD_BOM_ORG_T1
    WHERE stuee = v_current_level - 1
    )

    LOOP
    v_g_operation := 'TransformAlpgrToGroupLoop2';
    SELECT COUNT(alpgr)
    BULK COLLECT INTO v_alpgr_count_list
    FROM MD_BOM_ORG_T1
    WHERE father = father_rec.id
    AND alpgr IS NOT NULL
    GROUP BY alpgr;

    -- Calculate how many substitute groups the current father has
    v_group_total := 1;

    IF (v_alpgr_count_list.COUNT > 0) THEN
    FOR i IN 1 .. v_alpgr_count_list.COUNT
    LOOP
    v_g_operation := 'TransformAlpgrToGroupLoop3';
    v_group_total := v_group_total * v_alpgr_count_list(i);
    END LOOP;
    END IF;

    v_loop_count := v_group_total;
    -- Transform substitute item groups with the same father to substitute groups
    FOR alpgr_rec IN (SELECT DISTINCT alpgr
    FROM MD_BOM_ORG_T1
    WHERE father = father_rec.id
    AND alpgr IS NOT NULL
    )
    LOOP
    v_g_operation := 'TransformAlpgrToGroupLoop4';
    SELECT COUNT(alpgr) INTO v_alpgr_count
    FROM MD_BOM_ORG_T1
    WHERE father = father_rec.id
    AND alpgr = alpgr_rec.alpgr;

    v_loop_count := v_loop_count / v_alpgr_count;
    v_son_index := 0;
    FOR son_rec IN (SELECT id, stuee, father
    FROM MD_BOM_ORG_T1
    WHERE father = father_rec.id
    AND alpgr = alpgr_rec.alpgr
    )
    LOOP
    v_g_operation := 'TransformAlpgrToGroupLoop5';
    v_group_num_base := v_group_num + v_son_index * v_loop_count;
    FOR i IN 0 .. (v_group_total / (v_loop_count * v_alpgr_count) - 1)
    LOOP
    v_g_operation := 'TransformAlpgrToGroupLoop6';
    v_group_num_tmp := v_group_num_base + i * v_alpgr_count * v_loop_count;
    FOR j IN 1 .. v_loop_count
    LOOP
    v_g_operation := 'TransformAlpgrToGroupLoop7';
    INSERT INTO MD_COMPNT_SUBST_T1
    (sid, cid, stuee, gid)
    VALUES (son_rec.id, son_rec.father, son_rec.stuee, v_group_num_tmp + j);
    END LOOP;
    END LOOP;
    v_son_index := v_son_index + 1;
    END LOOP;
    END LOOP;

    v_g_operation := 'TransformAlpgrToGroupInsert1';
    INSERT INTO MD_COMPNT_SUBST_T1
    (sid, cid, stuee, gid)
    SELECT id, father, stuee, v_group_num + num
    FROM (SELECT ROWNUM AS num
    FROM MD_BOM_ORG_T1
    WHERE ROWNUM <= v_group_total
    ),
    MD_BOM_ORG_T1
    WHERE father = father_rec.id
    AND alpgr IS NULL;
    END LOOP;
    END LOOP;
    v_g_operation := 'TransformAlpgrToGroupInsert2';
    INSERT INTO MD_COMPNT_SUBST_T1
    (sid, cid, stuee, gid)
    SELECT id, father, stuee, 0
    FROM MD_BOM_ORG_T1
    WHERE stuee = 1;
    EXCEPTION
    WHEN OTHERS THEN
    WriteLog ('IDB_BR2_BOM.FPTransformAlpgrToGroup', v_g_operation, SQLCODE, SUBSTR(SQLERRM, 1, 512), 'E', NULL);
    COMMIT;
    END FPTransformAlpgrToGroup;

    -------------Code Ends


    Thanx in advance!

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You should also probably use an autonomous transaction to write the log record (if it is available in your version of oracle). Also if you remove the exception block (just to find this error of course) it might tell which line number its falling over on.

    Alan

  5. #5
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    Hi,

    I think your problemcan be with the v_loop_count variable. This is declared as a pls_integer. In the code one line says :
    v_loop_count := v_loop_count / v_alpgr_count

    This might result in a number with decimals (try 9/8) and it's put into an integer and that can cause your problem. Try writing a message with your writelog procedure to display this variable and its value and otherwise with all variables and their values. This might tell you which one is causing the problem
    Edwin van Hattem
    OCP DBA / System analyst

  6. #6
    Join Date
    Sep 2003
    Posts
    8

    but...

    Originally posted by AlanP
    You should also probably use an autonomous transaction to write the log record (if it is available in your version of oracle). Also if you remove the exception block (just to find this error of course) it might tell which line number its falling over on.

    Alan
    ok,on using the Writelog function,I try to record the exception when it raises,but remove the exception block will cause another problem that is the procedure will be called more than once,even one hundred times,it depends on the loop condition in the calling package,so when removed, it will cease to execute,so I can't find the detail yet.What shall I do, then?

    Thanx for your advice,
    Rgs,xujb

  7. #7
    Join Date
    Sep 2003
    Posts
    8

    Hi,

    Originally posted by evanhattem
    Hi,

    I think your problemcan be with the v_loop_count variable. This is declared as a pls_integer. In the code one line says :
    v_loop_count := v_loop_count / v_alpgr_count

    This might result in a number with decimals (try 9/8) and it's put into an integer and that can cause your problem. Try writing a message with your writelog procedure to display this variable and its value and otherwise with all variables and their values. This might tell you which one is causing the problem
    Thanx,evanhattem
    I will follow ur advice to try it,and to add more log information to check what is wrong,
    Rgs,xujb

  8. #8
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    For my solution,just use the writelog procedure to write the variables and their values to the exception table. Do not remove the exception for my solution
    But for alan's solution it will work ok if calling from a sql prompt. In that case SQL will note what line is causing the problem and by querying the all_source view you might be able to locate the line and it's text and therefor the cause of the problem
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

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