Oracle 19中查询为变量时DBMS_XMLGEN.getxml的JSON等效函数

• 现有公开同类问题的解答仅适用于查询语句为固定常量的场景，无法适配查询语句存放在变量中的动态场景
• 原生DBMS_XMLGEN.getxml方案可正常执行，但仅返回XML格式结果，示例代码如下：

SELECT XMLTYPE.createXML (DBMS_XMLGEN.getxml ('select  2 as a from dual')) FROM DUAL;

• 基于SQL_MACRO的实现仅支持Oracle 21c及以上版本，Oracle 19c无该特性无法使用，参考实现如下：

with FUNCTION f_test return varchar2 SQL_MACRO is
  query VARCHAR2(100) := 'select 1 a from dual';
  ret   VARCHAR2(100) := chr(13) || query || chr(13);
BEGIN
  RETURN ret;
END;
SELECT JSON_ARRAYagg(json_object(t.*))
  FROM f_test() t

• 在Oracle 19c中尝试直接通过WITH子句定义PL/SQL函数+动态SQL实现时，会触发类型错误，测试代码如下：

WITH
    FUNCTION f
        RETURN JSON_ARRAY
    IS
        query   VARCHAR2 (100) := 'select 1 from dual';
        l_str   VARCHAR2 (1000);
        l_cnt   JSON_ARRAY;
    BEGIN
        l_str :=
               'with from_dynamic_query as ('
            || query
            || ') SELECT JSON_ARRAYagg(json_object(*)) from from_dynamic_query';

        EXECUTE IMMEDIATE l_str
            INTO l_cnt;

        RETURN l_cnt;
    END;
SELECT *
  FROM DUAL;

执行后抛出错误：

[Error] Execution (20: 8): ORA-06553: PLS-313: 'F' not declared in this scope
ORA-06552: PL/SQL: Item ignored
ORA-06553: PLS-488: 'JSON_ARRAY' must be a type

核心需求：适配Oracle 19c版本，支持传入变量形式的查询语句，实现和DBMS_XMLGEN.getxml(query)能力等效的JSON结果生成。

Oracle 19c中WITH子句定义的PL/SQL函数无法直接将JSON_ARRAY作为返回类型（该类型为SQL层面的聚合构造器，非PL/SQL可直接声明的命名类型），且JSON结果本质为文本格式，可通过返回CLOB类型绕过类型限制，具体实现如下：

方案1：创建可复用持久函数 #

适合需要多次调用动态查询转JSON的场景：

CREATE OR REPLACE FUNCTION dyn_query_to_json(p_dyn_sql VARCHAR2)
RETURN CLOB
IS
    l_json_res CLOB;
BEGIN
    -- 可根据实际安全要求扩展SQL注入校验逻辑
    IF LENGTH(p_dyn_sql) > 32000 THEN
        RAISE_APPLICATION_ERROR(-20001, '查询语句长度超出允许范围');
    END IF;

    EXECUTE IMMEDIATE
        'SELECT JSON_ARRAYAGG(JSON_OBJECT(*) RETURNING CLOB) RETURNING CLOB FROM (' || p_dyn_sql || ')'
        INTO l_json_res;
    
    RETURN l_json_res;
END;
/

调用示例：

-- 测试简单查询
SELECT dyn_query_to_json('select 2 as a from dual') AS json_result FROM DUAL;

-- 测试业务表查询
-- SELECT dyn_query_to_json('select emp_id, emp_name from emp where dept_id = 10') FROM DUAL;

方案特点：

• 自动适配任意返回列数的动态查询，JSON_OBJECT(*)会自动读取查询返回的所有列生成对应JSON键值对
• 返回的CLOB格式JSON可被Oracle 19c所有原生JSON处理函数直接识别使用，和原生JSON类型无使用差异
• 生产环境使用必须补充SQL注入校验逻辑，禁止直接拼接未经过滤的外部输入

方案2：无持久对象的匿名块实现 #

适合无创建函数权限、仅需单次执行的场景：

SET SERVEROUTPUT ON;
DECLARE
    l_dyn_sql VARCHAR2(1000) := 'select 1 as col_a, ''test_val'' as col_b from dual';
    l_json_res CLOB;
BEGIN
    EXECUTE IMMEDIATE
        'SELECT JSON_ARRAYAGG(JSON_OBJECT(*) RETURNING CLOB) RETURNING CLOB FROM (' || l_dyn_sql || ')'
        INTO l_json_res;
    
    -- 此处添加自定义结果处理逻辑
    DBMS_OUTPUT.PUT_LINE(l_json_res);
END;
/

报错原因说明：原测试代码触发PLS-488错误的核心原因是错误将SQL函数JSON_ARRAY作为PL/SQL函数的返回值类型，PL/SQL引擎无法识别该类型定义，改用CLOB承载JSON文本即可解决该问题。

内容的提问来源于stack exchange，提问作者Pierre-olivier Gendraud