使用DBLINK执行带RETURNING子句的插入语句报错求助

问题场景 #

创建了带IDENTITY生成列的表，建表脚本如下：

CREATE TABLE "TABLESAMPLE"
(    "DESCRIPTION" VARCHAR2(2 BYTE),
"TID" NUMBER(5,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 99 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE
) SEGMENT CREATION DEFERRED

尝试通过dblink插入数据并返回自动生成的TID值，执行语句：

Insert into TABLESAMPLE@dblink1(DESCRIPTION) values('1') RETURNING tid INTO v_tid_return;

触发错误，系统提示：

*Cause: RETURNING clause is currently not supported for object type
columns, LONG columns, remote tables, INSERT with subquery,
and INSTEAD OF Triggers.

可行解决办法 #

方案1：远程存储过程封装插入逻辑 #

在远程数据库创建存储过程，封装插入操作并返回生成的TID：

-- 远程数据库执行：创建存储过程
CREATE OR REPLACE PROCEDURE INSERT_TABLESAMPLE(p_desc VARCHAR2, o_tid OUT NUMBER) AS
BEGIN
  INSERT INTO TABLESAMPLE(DESCRIPTION) VALUES(p_desc) RETURNING tid INTO o_tid;
  COMMIT; -- 根据业务实际需求决定是否提交事务
END;
/

本地调用远程存储过程获取ID：

DECLARE
  v_tid_return NUMBER;
BEGIN
  INSERT_TABLESAMPLE@dblink1('1', v_tid_return);
  DBMS_OUTPUT.PUT_LINE('生成的TID: ' || v_tid_return);
END;
/

方案2：插入后查询远程表最大值（低并发场景适用） #

如果业务场景并发量低，插入后直接查询远程表的TID最大值：

DECLARE
  v_tid_return NUMBER;
BEGIN
  INSERT INTO TABLESAMPLE@dblink1(DESCRIPTION) VALUES('1');
  SELECT MAX(tid) INTO v_tid_return FROM TABLESAMPLE@dblink1;
  DBMS_OUTPUT.PUT_LINE('生成的TID: ' || v_tid_return);
END;
/

注意：高并发场景下，该方法可能获取到其他会话插入的ID，结果不准确。

方案3：替换IDENTITY列为序列+触发器（允许修改表结构时使用） #

若可以修改远程表结构，将IDENTITY列替换为序列+触发器的组合，之后通过查询序列CURRVAL获取ID：

-- 远程数据库执行：创建序列
CREATE SEQUENCE SEQ_TABLESAMPLE MINVALUE 1 MAXVALUE 99 INCREMENT BY 1 START WITH 1 CACHE 20;

-- 修改表结构，移除IDENTITY属性
ALTER TABLE TABLESAMPLE MODIFY TID NUMBER(5,0) NOT NULL;

-- 创建触发器自动赋值
CREATE OR REPLACE TRIGGER TRG_TABLESAMPLE_TID
BEFORE INSERT ON TABLESAMPLE
FOR EACH ROW
BEGIN
  :NEW.TID := SEQ_TABLESAMPLE.NEXTVAL;
END;
/

本地插入后查询远程序列的当前值：

DECLARE
  v_tid_return NUMBER;
BEGIN
  INSERT INTO TABLESAMPLE@dblink1(DESCRIPTION) VALUES('1');
  SELECT SEQ_TABLESAMPLE.CURRVAL INTO v_tid_return FROM DUAL@dblink1;
  DBMS_OUTPUT.PUT_LINE('生成的TID: ' || v_tid_return);
END;
/

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