pl sql json code example

Example: 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.