1 create or replace
2 TYPE ty_row_str_split as object (strValue VARCHAR2 (4000));
3
4 create or replace
5 TYPE ty_tbl_str_split AS TABLE OF ty_row_str_split;
6 -------------------------------------------------------------
7
9 create or replace
10 FUNCTION fn_split(p_str IN VARCHAR2,
11 p_delimiter IN VARCHAR2)
12 RETURN ty_tbl_str_split IS
13 j INT := 0;
14 i INT := 1;
15 len INT := 0;
16 len1 INT := 0;
17 str VARCHAR2(4000);
18 str_split ty_tbl_str_split := ty_tbl_str_split();
19 BEGIN
20 len := LENGTH(p_str);
21 len1 := LENGTH(p_delimiter);
22
23 WHILE j < len LOOP
24 j := INSTR(p_str, p_delimiter, i);
25
26 IF j = 0 THEN
27 j := len;
28 str := SUBSTR(p_str, i);
29 str_split.EXTEND;
30 str_split(str_split.COUNT) := ty_row_str_split(strValue => str);
31
32 IF i >= len THEN
33 EXIT;
34 END IF;
35 ELSE
36 str := SUBSTR(p_str, i, j - i);
37 i := j + len1;
38 str_split.EXTEND;
39 str_split(str_split.COUNT) := ty_row_str_split(strValue => str);
40 END IF;
41 END LOOP;
42
43 RETURN str_split;
44 END fn_split;
45 ----------------------------
46
47
48 create or replace
49 FUNCTION parsejson(p_jsonstr varchar2,p_key varchar2) RETURN VARCHAR2
50 AS
51 rtnVal VARCHAR2(1000);
52 i NUMBER(2);
53 jsonkey VARCHAR2(500);
54 jsonvalue VARCHAR2(1000);
55 json VARCHAR2(3000);
56 BEGIN
57 IF p_jsonstr IS NOT NULL THEN
58 json := REPLACE(p_jsonstr,'{','') ;
59 json := REPLACE(json,'}','') ;
60 json := replace(json,'"','') ;
61 FOR temprow IN(SELECT strvalue AS VALUE FROM TABLE(fn_split(json, ','))) LOOP
62 IF temprow.VALUE IS NOT NULL THEN
63 i := 0;
64 jsonkey := '';
65 jsonvalue := '';
66 FOR tem2 IN(SELECT strvalue AS VALUE FROM TABLE(fn_split(temprow.value, ':'))) LOOP
67 IF i = 0 THEN
68 jsonkey := tem2.VALUE;
69 END IF;
70 IF i = 1 THEN
71 jsonvalue := tem2.VALUE;
72 END IF;
73
74 i := i + 1;
75 END LOOP;
76
77 IF(jsonkey = p_key) THEN
78 rtnVal := jsonvalue;
79 END if;
80 END IF;
81 END LOOP;
82 END IF;
83 RETURN rtnVal;
84 END parsejson;