pl 5csql parse json

1SQL> CREATE OR REPLACE FUNCTION parser ( p_json CLOB )
2 2                     RETURN CLOB AS
3 3
4 4     v_dg CLOB;
5 5     v_array JSON_ARRAY_T;
6 6     v_element JSON_ELEMENT_T;
7 7     v_ret_val VARCHAR2(1000);
8 8
9 9     e_no_more_elements EXCEPTION;
1010     PRAGMA EXCEPTION_INIT(e_no_more_elements,-30625); 
1111
1212   BEGIN
1313
1414     -- Yes, I selected from DUAL. Sorry. 
1515     -- I'll explain later. For now please just
1616     -- roll with it...
1717     SELECT JSON_DATAGUIDE(p_json)
1818       INTO v_dg
1919       FROM DUAL; 
2020 
2121     -- The dataguide is a JSON array so it loads 
2222     -- nicely into a local JSON_ARRAY_T variable 
2323     v_array := JSON_ARRAY_T(v_dg); 
2424 
2525     -- Loop through the elements and add them to the 
2626     -- return string. Stop when we run out of elements 
2727     -- and ORA-30625 is thrown. 
2828     DECLARE 
2929       v_counter NUMBER := 0; 
3030     BEGIN 
3131       LOOP 
3232         v_element := v_array.get(v_counter); 
3333         v_ret_val := v_ret_val || v_element.to_string; 
3434         v_counter := v_counter + 1; 
3535       END LOOP; 
3636     EXCEPTION 
3737       WHEN e_no_more_elements THEN 
3838         NULL; 
3939       WHEN OTHERS THEN 
4040        RAISE; 
4141     END; 
4242 
4343     -- send back the list of elements that defines 
4444     -- the list of elements - its all about schema 
4545     -- on demand (SOD). Never mind - I just made that up. 
4646     RETURN(v_ret_val); 
4747 
4848   END; 
4949  / 
50Function created.

1DECLARE 
2   j APEX_JSON.t_values; 
3   r_count number;
4   field5members   WWV_FLOW_T_VARCHAR2;
5   p0 number;
6   BrandId VARCHAR2(10);
7BEGIN
8APEX_JSON.parse(j,'<INSERT_JSON_STRING>');
9# Getting number of field3 elements
10r_count := APEX_JSON.GET_COUNT(p_path=>'field3',p_values=>j);
11dbms_output.put_line('Nr Records: ' || r_count);
12
13# Looping for each element in field3
14FOR i IN 1 .. r_count LOOP
15# Getting field5 members for the ith member of field3
16 field5members := APEX_JSON.GET_MEMBERS(p_path=>'field3[%d].field5',p_values=>j,p0=>i);
17# Looping all field5 members
18 FOR q in 1 .. field5members.COUNT LOOP
19# Extracting BrandId
20   BrandId := APEX_JSON.GET_VARCHAR2(p_path=>'field3[%d].field5.'||field5members(q) ,p_values=>j,p0=>i);
21# Printing BrandId and Product Id
22   dbms_output.put_line('Product Id ="'||field5members(q)||'" BrandId="'||BrandId ||'"');
23 END LOOP;
24END LOOP;
25   
26END;
27