Oracle 有没有办法将用户定义的类型转换为JSON？

有没有办法将用户定义的类型/集合转换为JSON (与通过XMLTYPE将它们转换为XML的方式相同)？

我不知道要做到这一点的内在方法。你可以接近的一种方法是 (从12.2):

-将类型转换为XML
-将此传递到XMLTable中。将元素标记转换为属性名称列。以及它们的内容到一个值列。
-使用json_objectagg将所有行合并为一个文档

这给出了类似的东西:

create or replace type user_type as object (
  attr1 varchar2(100),
  attr2 varchar2(100)
);
/

select *
from   xmltable (
  '/USER_TYPE/*'
  passing xmltype ( user_type ( 'val1', 'val2' ) )
  columns 
    att varchar2(100) path './name()',
    val varchar2(100) path './text()'
);

ATT     VAL    
ATTR1   val1   
ATTR2   val2  

select json_objectagg (
         att value val
       )
from   xmltable (
  '/USER_TYPE/*'
  passing xmltype ( user_type ( 'val1', 'val2' ) )
  columns 
    att varchar2(100) path './name()',
    val varchar2(100) path './text()'
);

JSON_OBJECTAGG(ATTVALUEVAL)       
{"ATTR1":"val1","ATTR2":"val2"} 

UPDATE 23 Oct 2019: 19c添加了自动将用户对象映射到JSON并再次映射的功能。

create or replace type user_type as object (
  attr1 varchar2(100),
  attr2 varchar2(100)
);
/

with obj as (
  select user_type ( 'this', 'that' ) o from dual
)
  select json_object ( o ) from obj;

JSON_OBJECT(O)                    
{"ATTR1":"this","ATTR2":"that"}  
  
create or replace type user_type_tab
  is table of user_type;
/

with obj as (
  select user_type_tab (
    user_type ( 'this', 'that' ),
    user_type ( 'another this', 'another that' )
  ) o 
  from dual
)
  select json_object ( o ) from obj;

JSON_OBJECT(O)                                                                      
[{"ATTR1":"this","ATTR2":"that"},{"ATTR1":"another this","ATTR2":"another that"}]