PL\SQL parse json code example
Example 1: PL\SQL parse json
SQL> CREATE OR REPLACE FUNCTION parser ( p_json CLOB )
2 RETURN CLOB AS
3
4 v_dg CLOB;
5 v_array JSON_ARRAY_T;
6 v_element JSON_ELEMENT_T;
7 v_ret_val VARCHAR2(1000);
8
9 e_no_more_elements EXCEPTION;
10 PRAGMA EXCEPTION_INIT(e_no_more_elements,-30625);
11
12 BEGIN
13
14 -- Yes, I selected from DUAL. Sorry.
15 -- I'll explain later. For now please just
16 -- roll with it...
17 SELECT JSON_DATAGUIDE(p_json)
18 INTO v_dg
19 FROM DUAL;
20
21 -- The dataguide is a JSON array so it loads
22 -- nicely into a local JSON_ARRAY_T variable
23 v_array := JSON_ARRAY_T(v_dg);
24
25 -- Loop through the elements and add them to the
26 -- return string. Stop when we run out of elements
27 -- and ORA-30625 is thrown.
28 DECLARE
29 v_counter NUMBER := 0;
30 BEGIN
31 LOOP
32 v_element := v_array.get(v_counter);
33 v_ret_val := v_ret_val || v_element.to_string;
34 v_counter := v_counter + 1;
35 END LOOP;
36 EXCEPTION
37 WHEN e_no_more_elements THEN
38 NULL;
39 WHEN OTHERS THEN
40 RAISE;
41 END;
42
43 -- send back the list of elements that defines
44 -- the list of elements - its all about schema
45 -- on demand (SOD). Never mind - I just made that up.
46 RETURN(v_ret_val);
47
48 END;
49 /
Function created.
Example 2: PL\SQL parse json
DECLARE
j APEX_JSON.t_values;
r_count number;
field5members WWV_FLOW_T_VARCHAR2;
p0 number;
BrandId VARCHAR2(10);
BEGIN
APEX_JSON.parse(j,'<INSERT_JSON_STRING>');
# Getting number of field3 elements
r_count := APEX_JSON.GET_COUNT(p_path=>'field3',p_values=>j);
dbms_output.put_line('Nr Records: ' || r_count);
# Looping for each element in field3
FOR i IN 1 .. r_count LOOP
# Getting field5 members for the ith member of field3
field5members := APEX_JSON.GET_MEMBERS(p_path=>'field3[%d].field5',p_values=>j,p0=>i);
# Looping all field5 members
FOR q in 1 .. field5members.COUNT LOOP
# Extracting BrandId
BrandId := APEX_JSON.GET_VARCHAR2(p_path=>'field3[%d].field5.'||field5members(q) ,p_values=>j,p0=>i);
# Printing BrandId and Product Id
dbms_output.put_line('Product Id ="'||field5members(q)||'" BrandId="'||BrandId ||'"');
END LOOP;
END LOOP;
END;