如何将主表自动生成的ID插入到其他数据库的关联表中

出现ORA-2055错误的核心原因有两个：

1. 序列值与实际生成的ID不匹配：主表LANGUAGE的ID是自动生成的identity列，你提前手动获取序列nextval，但插入主表时Oracle可能使用该序列的下一个值（或系统管理的序列），导致v_val和实际插入的ID不一致，同时这种手动取序列的操作会触发跨dblink的事务分支。
2. 跨多dblink的分布式事务问题：你的脚本同时操作Dblink1（主表插入）和Dblink2（关联表插入），属于分布式事务，一旦其中某个节点出现异常，就会触发事务回滚要求，而手动序列操作进一步增加了事务不一致的风险。

方案1：拆分事务，先插入主表并获取真实ID，再插入关联表 #

这种方式将跨dblink的操作拆分为两个独立事务，避免分布式事务的复杂性，同时确保获取的ID是主表实际生成的值：

declare
    v_language_id number;
begin
    -- 1. 插入主表并返回自动生成的ID
    insert into LANGUAGE@Dblink1 (CODE, DESCRIPTION, IS_ACTIVE)
    values ('NL11', 'NEDERLANDS', 1)
    returning ID into v_language_id;
    
    dbms_output.put_line('主表生成的ID: ' || v_language_id);
    
    -- 提交主表插入的事务
    commit;
    
    -- 2. 使用获取到的ID插入关联表（可循环处理其他4张类似表）
    insert into Customer_LANGUAGE@Dblink2 (ID, CODE, DESCRIPTION, IS_ACTIVE)
    values (v_language_id, 'NL11', 'NEDERLANDS', 1);
    
    -- 提交关联表插入的事务
    commit;
    
    dbms_output.put_line('关联表插入完成');
end;
/

注意事项 #

• 如果关联表插入失败，主表数据已经提交，需要根据业务需求添加补偿逻辑（比如删除主表数据或记录错误日志后续处理）。
• 这种方式适合业务上允许主表先存在，关联表后续补录的场景，或者能接受少量数据不一致时人工介入的情况。

方案2：使用自治事务处理关联表插入（保持主事务一致性） #

如果需要保证主表和关联表的插入要么都成功要么都失败，可以使用自治事务，但需要注意Oracle对分布式事务的限制：

declare
    v_language_id number;
    
    -- 定义自治事务过程，用于插入关联表
    procedure insert_customer_lang(p_id number) is
        pragma autonomous_transaction;
    begin
        insert into Customer_LANGUAGE@Dblink2 (ID, CODE, DESCRIPTION, IS_ACTIVE)
        values (p_id, 'NL11', 'NEDERLANDS', 1);
        commit;
    end;
begin
    -- 插入主表并获取ID
    insert into LANGUAGE@Dblink1 (CODE, DESCRIPTION, IS_ACTIVE)
    values ('NL11', 'NEDERLANDS', 1)
    returning ID into v_language_id;
    
    -- 调用自治事务插入关联表
    insert_customer_lang(v_language_id);
    
    -- 提交主表事务
    commit;
    
    dbms_output.put_line('所有插入操作完成，ID: ' || v_language_id);
exception
    when others then
        rollback;
        dbms_output.put_line('操作失败，已回滚: ' || sqlerrm);
        raise;
end;
/

注意事项 #

• 自治事务是独立的，即使主事务回滚，自治事务的提交也不会回滚，所以需要确保在主表插入成功后再调用自治事务。
• 这种方式能减少分布式事务的风险，但仍需测试dblink的事务兼容性。

关键优化点 #

• 避免手动获取序列值：identity列的ID由Oracle自动生成，必须通过returning子句获取插入后的真实ID，不能提前手动取序列nextval，否则会导致ID不匹配。
• 简化分布式事务：尽量将跨多个dblink的操作拆分为独立事务，降低两阶段提交失败的概率，除非业务强一致性要求必须用分布式事务。

内容的提问来源于stack exchange，提问作者coder11 b