Resolved: What is the analogous function to DBMS_XMLGEN.getxml(query) for json is the query is a variable and with oracle 19?

Question:

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

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

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

Answer:

create package pkg_ser as /*Package to implement PTF*/ function describe( tab in out dbms_tf.table_t ) return dbms_tf.describe_t ; procedure fetch_rows; end pkg_ser; /

create package body pkg_ser as function describe( tab in out dbms_tf.table_t ) return dbms_tf.describe_t as begin /*Mark input columns as used for subsequent row processing*/ for i in 1..tab.column.count loop tab.column(i).for_read := TRUE; end loop; /*Declare json output column*/ return dbms_tf.describe_t( new_columns => dbms_tf.columns_new_t( 1 => dbms_tf.column_metadata_t( name => ‘JSONVAL’, type => dbms_tf.type_varchar2 ) ) ); end; procedure fetch_rows /*Process rowset and serialize each row in JSON*/ as rowset dbms_tf.row_set_t; num_rows pls_integer; new_col dbms_tf.tab_varchar2_t; begin /*Get rows*/ dbms_tf.get_row_set( rowset => rowset, row_count => num_rows ); for rn in 1..num_rows loop /*Calculate new column value in the same row*/ new_col(rn) := dbms_tf.row_to_char( rowset => rowset, rid => num_rows, format => dbms_tf.FORMAT_JSON ); end loop; /*Put column to output*/ dbms_tf.put_col( columnid => 1, collection => new_col ); end; end pkg_ser; /

create function f_serialize_json(tab in table) /*Function to serialize into JSON using PTF*/ return table pipelined row polymorphic using pkg_ser; /

with function f_local_exec ( query in clob ) return varchar2 as ret varchar2(32000); begin /*Translate string to query using EXECUTE IMMEDIATE*/ execute immediate ‘ with a as ( ‘ || query || ‘ ) select json_arrayagg(jsonval format json) from f_serialize_json(a) ‘ into ret; return ret; end; select f_local_exec( ‘select level as id, mod(level, 3) as val from dual connect by level < 10’ ) as jsonval from dual | JSONVAL | | :—————————————————————————————————————————————————————— | | [{"ID":9, "VAL":0},{"ID":9, "VAL":0},{"ID":9, "VAL":0},{"ID":9, "VAL":0},{"ID":9, "VAL":0},{"ID":9, "VAL":0},{"ID":9, "VAL":0},{"ID":9, "VAL":0},{"ID":9, "VAL":0}] |

If you have better answer, please add a comment about this, thank you!