Home » SQL & PL/SQL » SQL & PL/SQL » how to overcome mutating table error (11g,toad 12.1)
how to overcome mutating table error [message #612043] Fri, 11 April 2014 06:49 Go to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
i have a trigger when should be fired there is an update on certain columns.this trigger calls another function which queries the same table.

CREATE OR REPLACE TRIGGER TAXI.VACANT_LIST
AFTER UPDATE OF LANDMARK,STATUS
ON TAXI.TAXI_VEHICLESTATUS 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE 
V_RET VARCHAR2(100);
BEGIN
IF (:NEW.LANDMARK <> :OLD.LANDMARK) AND (:NEW.STATUS <> :OLD.STATUS)
THEN
V_RET:= POST_VACANT_LIST(:NEW.VEHICLE_ID,:NEW.STATUS);
END IF;
END;
/


CREATE OR REPLACE FUNCTION TAXI.post_vacant_list 
(
p_vehicle_id IN VARCHAR2,
p_vehicle_status IN VARCHAR2
)
RETURN clob
IS
    XML CLOB;
    v_data_post CLOB;
    resp utl_http.resp;
    req  utl_http.req;
    v_txt CLOB;
    url varchar2(200);
BEGIN
    url := 'http://192.169.1.143/atlas/vacantlist.php';
    xml := JSON_UTIL_PKG.REF_CURSOR_TO_JSON(get_vacant_list_client(p_vehicle_id, p_vehicle_status));
    req  := UTL_HTTP.begin_request (url,'POST','HTTP/1.1');
    utl_http.set_header(req, 'Content-Type', 'application/x-www-form-urlencoded');
    utl_http.set_header(req, 'Content-Length', length(xml));
    v_data_post :=xml;
    utl_http.write_text(req, v_data_post);
    resp := UTL_HTTP.get_response(req);
    utl_http.read_text(resp,v_txt);
    utl_http.end_response(resp);
    dbms_output.put_line(v_txt);
    RETURN v_txt;
--exception when others then
 --   null;
  --  return 'error';
END;
/


CREATE OR REPLACE function get_vacant_list_client
(
p_vehicle_id varchar2,
p_vehicle_status varchar2
)
return sys_refcursor
as
l_returnvalue sys_refcursor;
begin
open l_returnvalue
for SELECT taxi_id, TAXI_NAME, VEHICLE_ID,LANDMARK,VEHICLE_TYPE, 
            DRIVER_NAME,START_DT,DRIVER_MOBILENO,STATUS FROM TAXI_VEHICLESTATUS, id where vehicle_id=p_vehicle_id and status=p_vehicle_status  AND
    start_dt = trunc (sysdate);
return l_returnvalue;
end;
/


update TAXI_VEHICLESTATUS set landmark='bangalore' ,status='Assigned' where vehicle_id=111; 

ORA-04091: table TAXI.TAXI_VEHICLESTATUS is mutating, trigger/function may not see it
ORA-06512: at "TAXI.GET_VACANT_LIST_CLIENT", line 10
ORA-06512: at "TAXI.POST_VACANT_LIST", line 16
ORA-06512: at "TAXI.VACANT_LIST", line 8
ORA-04088: error during execution of trigger 'TAXI.VACANT_LIST'
Re: how to overcome mutating table error [message #612049 is a reply to message #612043] Fri, 11 April 2014 07:49 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
> how to overcome mutating table error (11g,toad 12.1)
do NOT issue SQL against the same table upon which the Trigger is based.
Re: how to overcome mutating table error [message #612050 is a reply to message #612043] Fri, 11 April 2014 07:54 Go to previous messageGo to next message
flyboy
Messages: 1771
Registered: November 2006
Senior Member
The best way is NOT to query the table you are updating in the row-level (FOR EACH ROW) trigger. The correct approach depends on the requirements, which you did not post.

Just *think* about it: if you are updating three rows and all of them will fulfil the condition in the trigger, do you really want to run the procedure three times? If so, its first call would show only one updated value, the second call would show two and the third all three. Do you really want this behaviour? If so, you are out of luck, Oracle does not allow non-deterministic (as the order of updated rows is not defined) code flow.
Re: how to overcome mutating table error [message #612051 is a reply to message #612050] Fri, 11 April 2014 07:59 Go to previous messageGo to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
can i use pragma autonomous txn
Re: how to overcome mutating table error [message #612052 is a reply to message #612051] Fri, 11 April 2014 08:06 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
>can i use pragma autonomous txn
Yes, you can.
You can also poke yourself in the eye with a sharp pencil, but you are strongly advised to do NEITHER action.
Not every thing that you can do, should actually ever be done.
Re: how to overcome mutating table error [message #612164 is a reply to message #612051] Sun, 13 April 2014 23:22 Go to previous message
flyboy
Messages: 1771
Registered: November 2006
Senior Member
ashwanth77 wrote on Fri, 11 April 2014 14:59
can i use pragma autonomous txn

Depends on what you really want to achieve.

If your main goal is to "overcome mutating table error", then you can. But I have even better option for you: do not use that trigger at all.

If your main goal is to implement some functionality (not expressed in your posts), just be aware that this "overcoming" leads to these consequences:
- even after the UPDATE statement in the main transaction fails (rolls back), there will be no way to rollback changes inside the trigger as they already were commited
- no uncommited changes from the main transaction will be visible in the autonomous one, so in the autonomous transaction the table will appear to have the content before UPDATE statement
Note that this behaviour (using autonomous transaction) is different than without autonomous transaction which I described in my previous post. But, as you did not bother to comment it at all, it is impossible to deduce which functionality you want to implement (if any).

You may find more details about autonomous transactions in this article: http://www.orafaq.com/node/1915
Good luck.
Previous Topic: arrange
Next Topic: Historical Data Delete Query
Goto Forum:
  


Current Time: Thu Oct 02 06:22:39 CDT 2014

Total time taken to generate the page: 0.14467 seconds