Search notes:

Oracle JSON related PL/SQL types: SELECT INTO

Because of the ORA-40573: invalid use of PL/SQL JSON object type error, the result of json_arrayagg cannot be used in a PL/SQL select … into clause to create a json_array_t or json_object_t instance.

Rather, the result must first be selected into a clob and then the desired type be created:

declare
   res_clb clob;
   res_typ json_array_t;
begin
   select
      json_arrayagg(json_array(column_name, data_type)) into res_clb
   from
      all_tab_columns
   where
      table_name = 'USER_TAB_COLUMNS'
   ;
 
   res_typ := json_array_t.parse(res_clb);
  
   dbms_output.put_line(res_typ.to_string);
end;
/

Index