getting a Mutating error with a function:

From: NetComrade <andreyNSPAM_at_bookexchange.net>
Date: 2000/05/11
Message-ID: <391b3900.523779416@news.earthlink.net>#1/1

I've seen this stuff with triggers, but not with functions. And in the function I am not even looking at field I am modifying...

Any suggestions?

SQL> update manager set div_rank=div_rnk(100) where user_id=10   2 /
update manager set div_rank=div_rnk(100) where user_id=10

                            *

ERROR at line 1:

ORA-01001: invalid cursor
ORA-06512: at "SWBASEBALL.DIV_RNK", line 49
ORA-04091: table SWBASEBALL.MANAGER is mutating, trigger/function may

not see
it
ORA-06512: at line 1

CREATE OR REPLACE FUNCTION div_rnk (u IN NUMBER) RETURN NUMBER IS     user NUMBER;
    swp NUMBER;
    ranck NUMBER;
    prev_swp NUMBER;
    counter NUMBER;

    CURSOR c_div IS

        SELECT m.user_id, NVL(m.total_sw_points, 0)
        FROM manager m, user_group ug
        WHERE m.user_id = ug.user_id
          AND ug.group_id =
              (SELECT group_id
               FROM user_group
               WHERE user_id = u
                 AND rownum = 1)
        ORDER BY NVL(total_sw_points, 0) DESC;

BEGIN     ranck := 1;
    prev_swp := -10000;
    counter := 0;

    OPEN c_div;
    FETCH c_div INTO user, swp;

    WHILE c_div%FOUND LOOP

        IF swp != prev_swp THEN
            ranck := counter + 1;
        END IF;

        IF user = u THEN
            RETURN ranck;
        END IF;

        prev_swp := swp;
        counter := counter + 1;

        FETCH c_div INTO user, swp;

    END LOOP;
    CLOSE c_div;

    EXCEPTION
    WHEN OTHERS THEN
    CLOSE c_div;
    RAISE;
END div_rnk;

---

In case I forgot to mention:
We use Oracle 7.3.4 and 8.1.6 on Solaris 2.6, 2.7 boxes

---

Andrey Dmitriev	 eFax: (978) 383-5892  Daytime: (917) 750-3630
AOL: NetComrade	 ICQ: 11340726 remove NSPAM to email

Received on Thu May 11 2000 - 00:00:00 CDT