Oracle PL/SQL如何将函数内数组数据转为sys_refcursor输出

报错原因 #

你定义的t_num_array是MY_TEST_CLIENT包体内部的私有类型，Oracle SQL引擎无法识别PL/SQL私有类型，因此执行SELECT * FROM TABLE(l_result)时会抛出ORA-22905等类型不匹配错误。

解决方法 #

方案1：使用Oracle内置公开集合类型（修改成本最低） #

Oracle预定义了公开的数字集合类型sys.odcinumberlist，SQL引擎可直接识别，仅需修改包体代码即可：

create or replace PACKAGE BODY    MY_TEST_CLIENT
IS
    FUNCTION get_validated_values (p_input_begin  INT,
                                   p_input_end    INT)
                                   RETURN sys_refcursor
    IS
        l_current_value  INT;
        l_valid          INT;
        l_result_cursor  sys_refcursor;
        -- 直接使用Oracle内置的公开数字集合类型
        l_result         sys.odcinumberlist := new sys.odcinumberlist();
    BEGIN
        l_valid := 0;
        l_current_value := p_input_begin;

        LOOP
            MY_TEST.VALIDATE_MY_VALUE(l_current_value, l_valid);
            IF l_valid = 1 THEN
                l_result.extend();
                l_result(l_result.count) := l_current_value;
            END IF;
            EXIT WHEN l_current_value >= p_input_end;
            l_current_value := l_current_value + 1;
        END LOOP;

       open l_result_cursor for
        SELECT column_value FROM TABLE(l_result);
        return l_result_cursor;
    END;
END;
/

方案2：将自定义集合类型公开到包规范 #

如果需要保留自定义类型，把类型定义放到包规范中对外公开，SQL引擎即可识别：

1. 修改包规范：

create or replace PACKAGE    MY_TEST_CLIENT
IS
   TYPE t_num_array IS TABLE OF INTEGER;
   FUNCTION get_validated_values (p_input_begin  INT,
                                  p_input_end    INT)
                                RETURN sys_refcursor;
END;
/

2. 修改包体中打开游标的逻辑（加cast兼容低版本Oracle）：

open l_result_cursor for
SELECT column_value FROM TABLE(cast(l_result as t_num_array));

调用验证 #

执行如下语句即可得到预期的偶数结果：

-- 提取游标中的结果输出
SELECT column_value FROM TABLE(MY_TEST_CLIENT.get_validated_values(1,10));

输出结果为：2、4、6、8、10。

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