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.