Oracle数据库链路下CLOB字段跨库插入PL/SQL执行异常问题

在SQL Developer中直接执行以下包含CLOB字段（NARRATIVE）的跨库插入语句可正常运行：

INSERT INTO gtt_argus_narratives (
            case_id,
            abbrev_narrative,
            deleted,
            narrative,
            abbrev_narrative_j,
            narrative_j,
            dlp_revision_number,
            effective_start_date,
            effective_end_date,
            deleted_flag,
            enterprise_id
        ) 
            SELECT case_id,
            abbrev_narrative,
            deleted,
            narrative,
            abbrev_narrative_j,
            narrative_j,
            dlp_revision_number,
            effective_start_date,
            effective_end_date,
            deleted_flag,
            enterprise_id
        FROM ARGUS_INTEGRATION.DLP_CASE_NARRATIVE@ARGUS_TRANSCT_DB
        WHERE CASE_ID IN (SELECT DISTINCT CASE_ID FROM GT_ARGUS_LOCKS_TO_PROCESS);

但将该语句放入PL/SQL存储过程作为流程一部分执行时，抛出错误：

Error(563,13): PL/SQL: ORA-22992: cannot use LOB locators selected from remote tables

使用EXECUTE IMMEDIATE包裹语句可通过编译，但将查询改为与本地GTT（GT_ARGUS_LOCKS_TO_PROCESS）内连接时：

FROM ARGUS_INTEGRATION.DLP_CASE_NARRATIVE@ARGUS_TRANSCT_DB A
        INNER JOIN GT_ARGUS_LOCKS_TO_PROCESS B ON A.CASE_ID = B.CASE_ID AND A.DLP_REVISION_NUMBER = B.DLP_REVISION_NUMBER

即使使用EXECUTE IMMEDIATE仍执行失败，而IN子查询写法无异常。

ORA-22992错误源于PL/SQL上下文无法直接处理远程表返回的LOB定位符：

• 直接在SQL Developer执行时，SQL引擎会直接传输远程LOB的实际内容到本地，而非返回定位符
• 放入PL/SQL后，默认会尝试获取远程LOB的定位符，但PL/SQL不支持跨库使用这类定位符
• 使用IN子查询时，Oracle优化器可能将筛选逻辑推送到远程端，仅返回符合条件的完整数据（包括LOB内容）；但内连接本地GTT时，需要本地与远程数据做关联，导致LOB定位符被带入PL/SQL上下文，触发错误

方案1：将本地GTT数据同步到远程临时表（推荐，适合大数据量） #

如果有权限在远程库创建临时表，先把本地GTT的筛选数据同步到远程，再在远程完成关联查询：

-- 1. 在远程库创建临时表（仅需执行一次）
CREATE GLOBAL TEMPORARY TABLE ARGUS_INTEGRATION.GT_REMOTE_LOCKS_TO_PROCESS (
    CASE_ID NUMBER,
    DLP_REVISION_NUMBER NUMBER
) ON COMMIT DELETE ROWS;

-- 2. 在PL/SQL中先同步本地数据到远程临时表
INSERT INTO ARGUS_INTEGRATION.GT_REMOTE_LOCKS_TO_PROCESS@ARGUS_TRANSCT_DB
SELECT DISTINCT CASE_ID, DLP_REVISION_NUMBER FROM GT_ARGUS_LOCKS_TO_PROCESS;

-- 3. 执行跨库插入，使用远程临时表关联
INSERT INTO gtt_argus_narratives (
    case_id, abbrev_narrative, deleted, narrative,
    abbrev_narrative_j, narrative_j, dlp_revision_number,
    effective_start_date, effective_end_date, deleted_flag, enterprise_id
)
SELECT 
    A.case_id, A.abbrev_narrative, A.deleted, A.narrative,
    A.abbrev_narrative_j, A.narrative_j, A.dlp_revision_number,
    A.effective_start_date, A.effective_end_date, A.deleted_flag, A.enterprise_id
FROM ARGUS_INTEGRATION.DLP_CASE_NARRATIVE@ARGUS_TRANSCT_DB A
INNER JOIN ARGUS_INTEGRATION.GT_REMOTE_LOCKS_TO_PROCESS@ARGUS_TRANSCT_DB B 
    ON A.CASE_ID = B.CASE_ID AND A.DLP_REVISION_NUMBER = B.DLP_REVISION_NUMBER;

方案2：将CLOB转换为字符串（仅适合CLOB长度≤32767） #

如果CLOB内容长度不超过PL/SQL的VARCHAR2上限（12c及以上为32767），可显式将CLOB转为字符串，避免返回LOB定位符：

INSERT INTO gtt_argus_narratives (
    case_id, abbrev_narrative, deleted, narrative,
    abbrev_narrative_j, narrative_j, dlp_revision_number,
    effective_start_date, effective_end_date, deleted_flag, enterprise_id
)
SELECT 
    A.case_id, A.abbrev_narrative, A.deleted, TO_CHAR(A.narrative),
    TO_CHAR(A.abbrev_narrative_j), TO_CHAR(A.narrative_j), A.dlp_revision_number,
    A.effective_start_date, A.effective_end_date, A.deleted_flag, A.enterprise_id
FROM ARGUS_INTEGRATION.DLP_CASE_NARRATIVE@ARGUS_TRANSCT_DB A
INNER JOIN GT_ARGUS_LOCKS_TO_PROCESS B 
    ON A.CASE_ID = B.CASE_ID AND A.DLP_REVISION_NUMBER = B.DLP_REVISION_NUMBER;

方案3：使用动态SQL传递筛选参数（适合小数据量） #

如果本地GTT的记录数量较少，可将筛选条件拼接为IN列表，通过动态SQL执行：

DECLARE
    l_case_ids VARCHAR2(32767);
BEGIN
    -- 拼接CASE_ID和DLP_REVISION_NUMBER的组合条件
    SELECT LISTAGG('(' || CASE_ID || ',' || DLP_REVISION_NUMBER || ')', ',') WITHIN GROUP (ORDER BY CASE_ID)
    INTO l_case_ids
    FROM GT_ARGUS_LOCKS_TO_PROCESS;

    EXECUTE IMMEDIATE '
        INSERT INTO gtt_argus_narratives (
            case_id, abbrev_narrative, deleted, narrative,
            abbrev_narrative_j, narrative_j, dlp_revision_number,
            effective_start_date, effective_end_date, deleted_flag, enterprise_id
        )
        SELECT 
            case_id, abbrev_narrative, deleted, narrative,
            abbrev_narrative_j, narrative_j, dlp_revision_number,
            effective_start_date, effective_end_date, deleted_flag, enterprise_id
        FROM ARGUS_INTEGRATION.DLP_CASE_NARRATIVE@ARGUS_TRANSCT_DB
        WHERE (CASE_ID, DLP_REVISION_NUMBER) IN (' || l_case_ids || ')';
END;
/

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