使用Oracle PL/SQL表类型执行MERGE时出现“无效数据类型”错误的排查

错误原因 #

Oracle的SQL引擎无法识别PL/SQL包内定义的自定义记录类型和表类型。你在MERGE的USING子句中使用table(test_tab)，其中test_tab是包内的PL/SQL表类型，SQL引擎无法解析这种专属PL/SQL的类型，因此抛出00902: invalid datatype错误。

解决方案：使用SQL层面的对象类型 #

要在SQL语句中使用集合类型，必须创建SQL级别的对象类型和嵌套表类型，而非PL/SQL包内的类型。步骤如下：

1. 创建SQL对象类型（对应原test_rec记录类型）

CREATE OR REPLACE TYPE test_obj AS OBJECT (
    id NUMBER,
    name VARCHAR2(30),
    active_flag VARCHAR2(1)
);
/

2. 创建SQL嵌套表类型（对应原test_tab_type表类型）

CREATE OR REPLACE TYPE test_obj_tab AS TABLE OF test_obj;
/

3. 修改PL/SQL块，使用上述SQL类型并适配数据转换

SET SERVEROUTPUT ON;
DECLARE
    test_tab test_obj_tab;
    test_tab2 test_obj_tab;
BEGIN
    -- 直接将查询结果转换为SQL对象类型并批量收集
    SELECT test_obj(id, name, active_flag)
    BULK COLLECT INTO test_tab
    FROM ps_test01
    WHERE active_flag = 'Y';

    DBMS_OUTPUT.PUT_LINE('number of rows fetched : ' || test_tab.COUNT); 

    SELECT * BULK COLLECT INTO test_tab2 FROM TABLE(test_tab);
    DBMS_OUTPUT.PUT_LINE('number of rows fetched : ' || test_tab2.COUNT); 

    MERGE INTO ps_test02 tab2
        USING (SELECT id, name FROM TABLE(test_tab)) tab1
        ON (tab1.id = tab2.id)
        WHEN MATCHED THEN
            UPDATE SET name = tab1.name
        WHEN NOT MATCHED THEN
            INSERT (id, name) VALUES (tab1.id, tab1.name);
    
    COMMIT; -- 提交事务
END;
/

更优实现方案：直接关联源表 #

如果不需要对数据进行复杂的PL/SQL逻辑处理，完全可以跳过PL/SQL集合，直接在MERGE语句中关联源表ps_test01，这样代码更简洁、性能更优（避免内存加载数据的开销）：

MERGE INTO ps_test02 tab2
USING (
    SELECT id, name
    FROM ps_test01
    WHERE active_flag = 'Y'
) tab1
ON (tab1.id = tab2.id)
WHEN MATCHED THEN
    UPDATE SET name = tab1.name
WHEN NOT MATCHED THEN
    INSERT (id, name) VALUES (tab1.id, tab1.name);
COMMIT;

这种方式直接让Oracle优化器处理数据关联，不需要额外的PL/SQL集合转换，是此类场景下的最优实践。

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