how to overcome mutating table error [message #612043] |
Fri, 11 April 2014 06:49 |
|
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 #612050 is a reply to message #612043] |
Fri, 11 April 2014 07:54 |
flyboy
Messages: 1903 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 #612164 is a reply to message #612051] |
Sun, 13 April 2014 23:22 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
ashwanth77 wrote on Fri, 11 April 2014 14:59can 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.
|
|
|