.NET OracleDataReader调用json_array()返回空数组 与SQL Developer结果不一致

通过.NET客户端读取Oracle 21c数据库自定义对象集合时，因无法完成自定义类型映射，计划直接将结果以JSON格式返回，使用查询语句如下：

select json_array("UDTARR") from sys.typetest

• 在SQL Developer中执行上述查询可得到符合预期的正确结果
• 通过.NET端执行完全相同的查询时，读取到的结果仅为"[]"空数组
  补充说明：使用相同json_array()写法时，.NET端读取基础类型集合、同自定义对象下的非集合类型字段均可正常返回结果。

自定义类型定义 #

字段UDTARR关联的类型定义：

create type udtarray AS VARRAY(5) OF TEST_DATATYPEEX;

嵌套对象类型TEST_DATATYPEEX定义：

create type TEST_DATATYPEEX AS OBJECT
(test_id NUMBER,
vc VARCHAR2(20),
vcarray stringarray)

嵌套集合类型STRINGARRAY定义：

create type stringarray AS VARRAY(5) OF VARCHAR2(50);

.NET端查询代码 #

string query = "select json_array(\"UDTARR\") from sys.typetest"
using (var command = new OracleCommand(query, con))
using (var reader = command.ExecuteReader()){
    while (reader.Read()){
        Console.WriteLine(reader.GetString(0))
    }
}

审计日志对比 #

两次查询的连接用户均持有SYSDBA权限，审计日志记录如下：

• SQL Developer端审计记录：

Audit trail: LENGTH: '362' ACTION :[45] 'select json_array("UDTARR")
from sys.typetest' DATABASE USER:[3] 'SYS' PRIVILEGE :[6] 'SYSDBA'

• .NET端审计记录：

Audit trail: LENGTH: '361' ACTION :[45] 'select json_array("UDTARR")
from sys.typetest' DATABASE USER:[3] 'SYS' PRIVILEGE :[6] 'SYSDBA'
注：日志长度差1为换行符格式差异，不影响查询逻辑。

1. ODP.NET驱动版本不兼容
   Oracle 21c支持多层嵌套UDT直接序列化为JSON的特性，要求ODP.NET驱动版本不低于21.3。低于该版本的驱动无法正确识别多层嵌套的自定义对象结构，会直接将嵌套VARRAY解析为空集合返回，升级驱动到对应版本即可解决。
2. 缺少序列化显式声明
   默认json_array()调用未指定序列化规则时，部分版本ODP.NET会尝试在客户端侧做类型转换而非直接读取数据库返回的JSON结果，将查询修改为如下写法即可：

select json_array("UDTARR" FORMAT JSON, RETURNING CLOB) from sys.typetest

FORMAT JSON参数显式告知数据库输入为需要序列化的结构化对象，RETURNING CLOB避免返回结果长度受VARCHAR2类型限制，同时避免驱动对短字符串结果做错误的类型解析。
3. 连接字符串配置错误
检查连接字符串配置，确保未设置UDT Mapping Enabled=false，同时添加Json Serialization Mode=1参数，强制驱动直接返回数据库侧生成的原生JSON结果，不做客户端侧的类型映射转换。
4. 自定义类型权限缺失
即使连接用户持有SYSDBA权限，ODP.NET读取UDT元数据时不会完全继承SYSDBA的隐式权限，需要给当前连接用户显式授予三个自定义类型的EXECUTE权限，否则驱动因无法读取嵌套类型元数据会返回空集合。

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