I utilize Pragma Autonomous_Transaction during a trigger as it updates a remote MS SQL Server table via a remote procedure using Execute_Immediate. Without the autonomous_transaction enabled I receive error - "ORA-02047: cannot join the distributed transaction in progress". That part works OK. However, early in the trigger I need to query another table based upon a :NEW.<value> to get the input needed for the remote procedure call. With autonomous_transaction enabled the query returns "ORA-01403: no data found". Without enabled the query returns the correct data, but the remote call errors (as earlier mentioned).
There is an application that inserts data into the table that the trigger is on. I believe that the application may be transactionally updating both the trigger table and the table I'm trying to query, though it definitely adds data to the query table first based upon the table relationship. I'm not sure if the autonomous_transaction makes the query table data not committed / available yet or what.
Due to the remote query I could not think of a way to provide a LiveSQL example.
create or replace TRIGGER TR_TABLE2_BEFORE_INSERT
BEFORE INSERT
ON Table2
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
MyException EXCEPTION;
MyVar varchar2(30);
BEGIN
-- With PRAGMA AUTONOMOUS_TRANSACTION - QUERY returns No Data Found, returns fine otherwise
-- Tested Static Var without query and remote proc completes successfully
-- QUERY
SELECT MyAppField1 INTO MyVar FROM Table1 WHERE Table1.xID = :NEW.xID;
-- STATIC VAR
MyVar := 'XYZ';
IF MyVar = 'XYZ' THEN
DECLARE
sDateTime VARCHAR2(100);
iSQLPassthrough INTEGER;
BEGIN
sDateTime := to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ss');
iSQLPassthrough := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@REMOTE_SQLSERVER('UpdateMyStatus "'||MyVar||'" , "'||sDateTime||'"' );
END IF;
COMMIT;
EXCEPTION
WHEN MyException THEN
BEGIN
-- CUSTOM LOG WRITER PROC
END;
WHEN OTHERS THEN
g_err_code := SQLCODE;
g_err_msg := SUBSTR(SQLERRM, 1, 2000);
BEGIN
-- CUSTOM LOG WRITER PROC
END;
END;
Thanks in advance for any help you can provide.
Bob
By any chance are you inserting xID in both table1 & 2 in the same transaction?
Because an autonomous transaction can only see committed data. Uncommitted changes in your session are invisible to it:
create table t1 (
x int
);
create table t2 (
x int
);
create or replace trigger trg
before insert on t2 for each row
declare
t1_x t1.x%type;
pragma autonomous_transaction;
begin
select * into t1_x from t1
where t1.x = :new.x;
dbms_output.put_line ( 'Found ' || t1_x );
commit;
end;
/
insert into t1 values (1);
insert into t2 values (1);
ORA-01403: no data found
commit;
insert into t2 values (1);
Found 1
select * from t1;
X
1
select * from t2;
X
1