能否使用json_object(*)将PL/SQL record/collection转为JSON对象

能不能直接用，核心看你运行的Oracle数据库版本：

• Oracle 21c及以上：完全可行。21c开始Oracle做了类型系统打通，PL/SQL块里声明的record、关联数组/嵌套表/varray这类局部集合类型，SQL引擎可以直接识别，传入json_object(*)就会自动按字段名、集合结构转成对应的JSON对象/数组，不需要额外处理。
• 19c及更低版本：直接用不了。这些版本的json_object(*)只认SQL层面定义的类型——要么是数字、字符串这类标量，要么是用CREATE TYPE提前创建的SQL级对象、集合类型，PL/SQL内部声明的局部类型它识别不了，硬传会报类型不匹配的编译或者运行错误。

如果用的是21c以前的版本，选下面任意一种方案都能稳定实现需求：

• 原生JSON类型API手动构造（12cR2及以上通用，无额外依赖）
  12cR2开始Oracle自带了json_object_t（对应JSON对象）、json_array_t（对应JSON数组）这几个内置类型，你只要把record的字段挨个put到对象实例里，集合元素遍历后挨个append到数组实例里，最后调to_clob之类的方法就能直接输出合法JSON，特殊字符转义、嵌套结构处理都是内置的，不会出格式问题。给个最简单的示例：

  DECLARE
    -- PL/SQL内部定义的record和集合
    TYPE emp_rec IS RECORD (
      emp_id   NUMBER,
      emp_name VARCHAR2(50),
      dept     VARCHAR2(30)
    );
    TYPE emp_list IS TABLE OF emp_rec;
    v_emps emp_list;
    v_arr  json_array_t := json_array_t();
    v_res  CLOB;
  BEGIN
    -- 填充测试数据
    v_emps := emp_list(
      emp_rec(1001, '张三', '研发部'),
      emp_rec(1002, '李四', '产品部')
    );
    -- 遍历转换
    FOR i IN 1..v_emps.count LOOP
      DECLARE
        v_obj json_object_t := json_object_t();
      BEGIN
        v_obj.put('emp_id', v_emps(i).emp_id);
        v_obj.put('emp_name', v_emps(i).emp_name);
        v_obj.put('dept', v_emps(i).dept);
        v_arr.append(v_obj);
      END;
    END LOOP;
    v_res := v_arr.to_clob;
    DBMS_OUTPUT.put_line(v_res);
  END;
  /
  

• 映射SQL层类型后用json_object(*)转换（12cR2及以上）
  如果觉得逐字段写太麻烦，可以先在SQL层用CREATE TYPE建一套和PL/SQL里的record、集合结构完全一致的SQL级类型，转换前先把PL/SQL变量的值赋值给对应SQL类型的实例，之后就可以直接用json_object(*)、json_array(*)自动序列化，适合结构固定、转换逻辑频繁调用的场景。
• APEX_JSON包转换（11g及以上，需已安装Oracle APEX）
  要是用的是11g这类更老的版本，且环境装了Oracle APEX组件，直接用apex_json的API构造就行，逻辑和原生JSON类型API差不多，不用自己拼字符串，能避开大部分格式错误。

别直接手动拼字符串生成JSON，只要字段值里有双引号、换行、特殊字符大概率会出现非法格式的问题，后期维护也麻烦。

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