Oracle 19C 将 select 查询解决转换为 JSON 格式

我利用这种转换，将查询结果存储在json文件中，利用python可以特别方便的解析json文件来对数据库进行巡检或收集相关信息。

• 以字典形式存放查询结果

select JSON_OBJECT(*) from v$database;

结果示例：

{
    "DBID": 2241305521,
    "NAME": "ORCL",
    "CREATED": "2021-11-10T17:34:41",
    "PRIOR_RESETLOGS_TIME": null
}

• 自定义字典的key值

SELECT JSON_OBJECT(
'log_mode' : name||' is '||log_mode||' mode;',
'force_logging' : force_logging,
'supplemental_log_data_min' : supplemental_log_data_min
)
FROM v$database;

结果示例：

{
    "log_mode": "BILL is ARCHIVELOG mode;",
    "force_logging": "YES",
    "supplemental_log_data_min": "YES"
}

• 将某一字段以列表形式存放，同时可以排序

SQL> select JSON_ARRAYAGG(INSTANCE_NUMBER ORDER BY INSTANCE_NUMBER) from gv$instance;

[1,2]

SQL> select JSON_ARRAYAGG(INSTANCE_NUMBER ORDER BY INSTANCE_NUMBER desc) from gv$instance;

[2,1]

• 组合使用

select JSON_ARRAYAGG (JSON_OBJECT(*)) from gv$instance;

结果示例：

[
    {
        "INST_ID": 1,
        "INSTANCE_NUMBER": 1,
        "DATABASE_TYPE": "RAC"
    },
    {
        "INST_ID": 2,
        "INSTANCE_NUMBER": 2,
        "DATABASE_TYPE": "RAC"
    }
]

SQL> select JSON_ARRAYAGG (JSON_OBJECT(*)) from gv$osstat;
select JSON_ARRAYAGG (JSON_OBJECT(*)) from gv$osstat
*
ERROR at line 1:
ORA-40478: output value too large (maximum: 4000)

--ORA-40478 or ORA-40459 running JSON Generation Functions (Doc ID 2354511.1)
SQL> select JSON_ARRAYAGG(JSON_OBJECT(*) returning varchar2(32767)) from gv$osstat;