Parse JSON array in PL/SQL
I think your JSON string should be like this:
[{"source": "00000999","message": "test1"}, {"source": "00000999","message": "test2"}, {"source": "00000999","message": "test3"}, {"source": "00000999","message": "test4"}]Try this:
DECLARE items VARCHAR2(2000):= '{"items":[{"source": "00000999","message": "test1"}, {"source": "00000999","message": "test2"}, {"source": "00000999","message": "test3"}, {"source": "00000999","message": "test4"}]}'; my_json json_list := json_list(LTRIM(RTRIM(items,'}'), '{"items":')); listElement json_value; jsonObj json;BEGIN FOR i IN 1..my_json.count LOOP listElement := my_json.get(i); jsonObj := json(listElement); dbms_output.put_line(jsonObj.json_data(1).mapname); dbms_output.put_line(jsonObj.json_data(1).str); END LOOP;END;
did you try to use apex_json package in 5.0by using it you can convert your data to an adhoc xmltable and insert into your permanent table with INSERT INTO SELECT concept.you can see the sample of apexjson and xml table at