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

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