Oracle 使用Pragma Autonomous_Transaction触发期间找不到相关事务表数据

askTom 2018-02-26

494

问题描述

我在触发器期间使用Pragma autonousous_transaction，因为它通过使用Execute_Immediate的远程过程更新远程MS SQL Server表。没有启用autonomous_transaction，我收到错误-“ORA-02047: 无法加入正在进行的分布式事务”。那部分工作正常。但是，在触发器的早期，我需要根据a :NEW查询另一个表。以获取远程过程调用所需的输入。启用autonomous_transaction后，查询返回 “ORA-01403: 未找到数据”。没有启用查询返回正确的数据，但远程调用错误 (如前所述)。

有一个应用程序将数据插入触发器所在的表中。我相信应用程序可能会对触发器表和我要查询的表进行事务性更新，尽管它肯定会根据表关系将数据添加到查询表中。我不确定autonous_transaction是否使查询表数据尚未提交/可用或什么。

由于远程查询，我无法想到提供LiveSQL示例的方法。

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;

提前感谢您提供的任何帮助。
鲍勃

专家解答

您是否有机会在同一事务中的table1和2中插入xID？

因为自治事务只能看到已提交的数据。会话中的未提交更改对它来说是不可见的:

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

oracle asktom

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 使用Pragma Autonomous_Transaction触发期间找不到相关事务表数据

问题描述

专家解答

评论