Oracle parsing JSON format

1.

CREATE OR REPLACE Package Pkg_analyse_common is

--Author:yzchen
--CREATED:2013/11/26 14:12:43
--PURPOSE: public Stored Procedure Package

/*
* Author:yzchen datetime:2013-11-26 14:20:36
* DESC: Split string According to P_seq, and return data format, default to, split
*/
--Split string temporary storage type
TYPE Type_split is TABLE of VARCHAR2 (1024);
--Split function
FUNCTION Func_split (p_list VARCHAR2, p_sep VARCHAR2: = ', ')
RETURN Type_split
pipelined;

/*
* Author:yzchen datetime:2013-11-27 17:20:36
* DESC: Parse the specified JSON format string
*/
--Analytic functions
FUNCTION Func_parsejson (p_jsonstr VARCHAR2) RETURN type_split
pipelined;

--Parse the function and get the value of the specified key
FUNCTION Func_parsejson_bykey (p_jsonstr VARCHAR2, P_key VARCHAR2)
RETURN VARCHAR2;

END Pkg_analyse_common;

2.

CREATE OR REPLACE Package BODY Pkg_analyse_common is

/*
* @SEE declaretion
*/
FUNCTION Func_split (p_list VARCHAR2, p_sep VARCHAR2: = ', ')
RETURN Type_split
Pipelined is
L_idx Pls_integer;
V_list VARCHAR2 (4000): = P_list;
BEGIN
LOOP
L_idx: = INSTR (V_list, P_SEP);
IF l_idx > 0 Then
PIPE ROW (SUBSTR (v_list, 1, l_idx-1));
V_list: = SUBSTR (v_list, L_idx + LENGTH (p_sep));
ELSE
PIPE ROW (v_list);
EXIT;
END IF;
END LOOP;
END Func_split;

/*
* @SEE declaretion
*/
FUNCTION Func_parsejson (p_jsonstr VARCHAR2) RETURN type_split
pipelined is
V_jsonstr VARCHAR2 (+): = P_jsonstr;
Jsonkey VARCHAR2 (50);
Jsonvalue VARCHAR2 (50);
JSON VARCHAR2 (1000);
Tempchar VARCHAR2 (1);
TEMPSTR1 VARCHAR2 (1000);
TEMPSTR2 VARCHAR2 (1000);
Cur_json1 Sys_refcursor;
Cur_json2 Sys_refcursor;
BEGIN
IF v_jsonstr is no NULL then
--first remove the preceding [and later] symbols
Tempchar: = SUBSTR (V_jsonstr, 1, 1);
IF ' [' = Tempchar then
v_jsonstr: = SUBSTR (V_jsonstr, 2, LENGTH (V_JSONSTR));
END IF;
Tempchar: = SUBSTR (V_jsonstr, LENGTH (V_JSONSTR), 1);
IF '] ' = Tempchar then
v_jsonstr: = SUBSTR (V_jsonstr, 1, LENGTH (V_JSONSTR)-1);
END IF;

--Start parsing
JSON: = REPLACE (V_jsonstr, ' {', ');
JSON: = REPLACE (JSON, '} ', ');
JSON: = REPLACE (JSON, ' "', ');
OPEN Cur_json1 for
SELECT * from TABLE (Pkg_analyse_common. Func_split (JSON, ', '));
LOOP
FETCH Cur_json1
into TEMPSTR1;
EXIT when Cur_json1%notfound;

IF TEMPSTR1 is isn't NULL then
Jsonkey: = ";
Jsonvalue: = ";
OPEN Cur_json2 for
SELECT * from TABLE (Pkg_analyse_common. Func_split (TEMPSTR1, ': '));
LOOP
FETCH Cur_json2
into TEMPSTR2;
EXIT when Cur_json2%notfound;
PIPE ROW (TEMPSTR2);
END LOOP;
END IF;

END LOOP;
END IF;
END Func_parsejson;

/*
* @SEE declaretion
*/
FUNCTION Func_parsejson_bykey (p_jsonstr VARCHAR2, P_key VARCHAR2)
RETURN Varc HAR2 is
V_jsonstr VARCHAR2 (4000): = P_JSONSTR;
Jsonkey VARCHAR2 (50);
Jsonvalue VARCHAR2 (50);
JSON VARCHAR2 (4000);
Tempchar VARCHAR2 (1);
TEMPSTR1 VARCHAR2 (4000);
TEMPSTR2 VARCHAR2 (4000);
Cur_json1 Sys_refcursor;
Cur_json2 Sys_refcursor;
IDX Number: = 0;
BEGIN
IF v_jsonstr is no NULL then
--first remove the preceding [and later] symbols
Tempchar: = SUBSTR (V_jsonstr, 1, 1);
IF ' [' = Tempchar then
v_jsonstr: = SUBSTR (V_jsonstr, 2, LENGTH (V_JSONSTR));
END IF;
Tempchar: = SUBSTR (V_jsonstr, LENGTH (V_JSONSTR), 1);
IF '] ' = Tempchar then
v_jsonstr: = SUBSTR (V_jsonstr, 1, LENGTH (V_JSONSTR)-1);
END IF;

--Start parsing
JSON: = REPLACE (V_jsonstr, ' {', ');
JSON: = REPLACE (JSON, '} ', ');
JSON: = REPLACE (JSON, ' "', ');
OPEN Cur_json1 for
SELECT * from TABLE (Pkg_analyse_common. Func_split (JSON, ', '));
LOOP
FETCH Cur_json1
into TEMPSTR1;
EXIT when Cur_json1%notfound;
IDX: = 0;
IF TEMPSTR1 is isn't NULL then
Jsonkey: = ";
Jsonvalue: = ";
OPEN Cur_json2 for
SELECT * from TABLE (Pkg_analyse_common. Func_split (TEMPSTR1, ': '));
LOOP
FETCH Cur_json2
into TEMPSTR2;
EXIT when Cur_json2%notfound;
IF IDX > 0 Then
RETURN TEMPSTR2;
END IF;
IF TEMPSTR2 = P_key Then
IDX: = idx + 1;
END IF;
END LOOP;
END IF;

END LOOP;
END IF;
RETURN ';
END Func_parsejson_bykey;

END Pkg_analyse_common;

Oracle parsing JSON format