Re: getting a Mutating error with a function:

On Thu, 11 May 2000 22:45:33 GMT, andreyNSPAM_at_bookexchange.net (NetComrade) wrote:

>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

Yes but you are SELECTing from the table you are modifying, which is not allowed. The function looks IMO also very shaky to me, a hardcoded rownum = 1 in a subquery, a loop backing out on user= u while that u is contained in the subquery? Brrr. What are you trying to accomplish? Obviously you to have a proper subquery in your update, and from your post it is not clear the function can't be replaced by one single select. Sometimes one regrets pl/sql was introduced at all, because many times people resolve procedural, what could have been done relational.

Regards,

Sybrand Bakker, Oracle DBA Received on Fri May 12 2000 - 00:00:00 CDT