If you really want a variable number of columns then you will either need to do a dynamic pivot of a column-based result (such as from @MTO's answer), or generate a dynamic json_table.
You can use json_dataguide() to generate the schema for a specific Vals array, and then pass that through its own json_table and loop to generate the columns clause names, types and paths.
This example regenerates your original query, except it used a bind variable for the JSON string instead of a CTE; and then opens that using the same string that was used for the data guide.
create or replace function dynamic_parse(sJson clob)
return sys_refcursor as
sGuide clob;
sSQL clob;
rc sys_refcursor;
begin
-- initial static part of query
sSQL := q'^select jt.*
from json_table (:sJson, '$'
columns
ID varchar2(32) path '$.ID',
ID_ORD varchar2(32) path '$.ID_ORD',
nested path '$.Vals[*]'
columns (^';
select json_dataguide(jt.vals)
into sGuide
from json_table (sJson, '$'
columns
VALS clob format json path '$.Vals'
) jt;
for r in (
select jt.*
from json_table (sGuide format json, '$[*]'
columns
indx for ordinality,
path varchar2(30) path '$."o:path"',
type varchar2(30) path '$.type',
length number path '$."o:length"'
) jt
)
loop
sSQL := sSQL || case when r.indx > 1 then ',' end
|| chr(10) || ' '
|| '"' || substr(r.path, 3) || '"'
-- may need to handle other data type more carefully too
|| ' ' || case when r.type = 'string' then 'varchar2(' || r.length || ')' else r.type end
|| q'^ path '^' || r.path || q'^'^';
end loop;
-- final static part of query
sSQL := sSQL || chr(10) || ' )) jt';
dbms_output.put_line(sSQL);
open rc for sSQL using sJson;
return rc;
end;
/
db<>fiddle showing some of the steps, the generated dynamic SQL statement, and how you could use that to open a ref cursor. The generated dynamic statement comes out as:
select jt.*
from json_table (:sJson, '$'
columns
ID varchar2(32) path '$.ID',
ID_ORD varchar2(32) path '$.ID_ORD',
nested path '$.Vals[*]'
columns (
"CODE" varchar2(16) path '$.CODE',
"DORD" varchar2(16) path '$.DORD'
)) jt
It also shows a dummy anonymous block that calls the function and prints the ref cursor contents (because db<>fiddle doesn't support select func_returning_ref_cursor from dual which you can do in SQL Developer etc.) as:
1444284517:4255;2187606199:ONB2B3BB8:25.04.2021
... but that demonstrates one of the problems with this approach: the caller has to know the number and types of columns in advance, or itself has to use some element of dynamic handling.
You may also want to explore the wider JSON Data Guide functionality.
