Oracle无Create type权限下函数返回表类型的解决方案问询

我们的产品包含多个可独立安装的组件，支持Oracle数据库，但默认不授予CREATE TYPE权限。其中一个组件的安装流程需要客户先安装组件、临时添加CREATE TYPE权限后再运行该组件。

我原本计划在创建函数的SQL文件中使用以下代码：

CREATE OR REPLACE FUNCTION get_some_data (input INT) RETURN my_table AS 
  my_table_var my_table := my_table (); 
  ret_code INT := 0; 
BEGIN 
  ret_code := create_my_type_and_table (); 
  IF 1 = ret_code THEN 
    NULL; -- add some data to my_table_var here 
  END IF; 
  RETURN my_table_var; 
END get_some_data; 
/

其中create_my_type_and_table函数会通过EXECUTE IMMEDIATE创建记录和表类型。但显然存在问题：get_some_data函数声明返回my_table类型，但该类型在编译时还未创建，导致编译失败。

我有两个疑问：

1. 是否有解决该编译问题的办法？
2. 我需要返回多个INT类型字段才打算创建并返回表类型，是否可以返回多维数组或系统集合？我尝试过sys.odcinumberlist，但未找到返回4列sys.odcinumberlist的方法。

问题1：解决编译失败的办法 #

核心问题是函数编译时依赖的my_table类型尚未存在，我们可以通过两种思路解决：

方案1：先确保类型存在，再编译函数 #

把类型检查、创建和函数编译封装在一个PL/SQL块中，通过动态SQL保证类型创建在函数编译之前：

DECLARE
  l_type_count NUMBER;
BEGIN
  -- 检查my_table类型是否已存在
  SELECT COUNT(1)
  INTO l_type_count
  FROM user_types
  WHERE UPPER(type_name) = 'MY_TABLE';

  -- 不存在则先创建依赖的记录类型和表类型
  IF l_type_count = 0 THEN
    EXECUTE IMMEDIATE 'CREATE TYPE my_record AS OBJECT (col1 INT, col2 INT, col3 INT, col4 INT)';
    EXECUTE IMMEDIATE 'CREATE TYPE my_table AS TABLE OF my_record';
  END IF;

  -- 动态创建get_some_data函数
  EXECUTE IMMEDIATE '
CREATE OR REPLACE FUNCTION get_some_data (input INT) RETURN my_table 
AS 
  my_table_var my_table := my_table (); 
  ret_code INT := 0; 
BEGIN 
  ret_code := create_my_type_and_table (); 
  IF 1 = ret_code THEN 
    -- 示例：添加数据到集合
    my_table_var.EXTEND;
    my_table_var(my_table_var.LAST) := my_record(1,2,3,4);
  END IF; 
  RETURN my_table_var; 
END get_some_data;';
END;
/

这样执行时会先完成类型创建，再编译函数，从根源上避免编译失败。

方案2：改用SYS_REFCURSOR作为返回类型 #

如果不想依赖自定义类型，可以使用Oracle自带的SYS_REFCURSOR，它不需要CREATE TYPE权限，也无需提前定义任何类型：

CREATE OR REPLACE FUNCTION get_some_data (input INT) RETURN SYS_REFCURSOR
AS 
  cur SYS_REFCURSOR;
BEGIN
  OPEN cur FOR
    -- 替换为你的业务逻辑，返回4列INT数据
    SELECT 1 AS col1, 2 AS col2, 3 AS col3, 4 AS col4 FROM DUAL
    UNION ALL
    SELECT 5 AS col1, 6 AS col2, 7 AS col3, 8 AS col4 FROM DUAL;
  
  RETURN cur;
END get_some_data;
/

客户端可以直接读取游标中的多列数据，完全绕开自定义类型的依赖。

问题2：替代自定义表类型的返回方案 #

Oracle没有原生的多维系统集合，但有几种方案可以避免自定义类型：

方案1：嵌套系统集合（需临时CREATE TYPE权限） #

虽然不能直接返回4列SYS.ODCINUMBERLIST，但可以创建一个包含SYS.ODCINUMBERLIST的嵌套集合，每个子集合代表一行的4个INT值：

-- 先创建嵌套集合类型（需要CREATE TYPE权限）
CREATE TYPE number_list_table AS TABLE OF SYS.ODCINUMBERLIST;

-- 定义函数
CREATE OR REPLACE FUNCTION get_some_data (input INT) RETURN number_list_table
AS 
  result number_list_table := number_list_table();
BEGIN
  -- 添加第一行（4个INT值）
  result.EXTEND;
  result(result.LAST) := SYS.ODCINUMBERLIST(10,20,30,40);
  
  -- 添加第二行
  result.EXTEND;
  result(result.LAST) := SYS.ODCINUMBERLIST(50,60,70,80);
  
  RETURN result;
END get_some_data;
/

客户端读取时，每个元素对应一行的4列数据。

方案2：返回JSON格式的CLOB #

如果客户端支持JSON解析，可以将多列数据序列化为JSON数组返回，无需任何自定义类型：

CREATE OR REPLACE FUNCTION get_some_data (input INT) RETURN CLOB
AS 
  result_clob CLOB;
BEGIN
  -- 构造包含多列数据的JSON数组
  SELECT JSON_ARRAY(
    JSON_OBJECT('col1' VALUE 1, 'col2' VALUE 2, 'col3' VALUE 3, 'col4' VALUE 4),
    JSON_OBJECT('col1' VALUE 5, 'col2' VALUE 6, 'col3' VALUE 7, 'col4' VALUE 8)
  ) INTO result_clob FROM DUAL;
  
  RETURN result_clob;
END get_some_data;
/

返回结果类似：[{"col1":1,"col2":2,"col3":3,"col4":4},{"col1":5,"col2":6,"col3":7,"col4":8}]，客户端可轻松解析。

方案3：优先使用SYS_REFCURSOR #

如问题1中的方案2，SYS_REFCURSOR是Oracle返回多列数据的常规方式，无需额外权限，也不用处理复杂的集合或JSON，是最省心的选择。

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