Hi All,
I am trying to parse the below json output using pl/sql and insert the data into a database table. I did researched on the forum and found a very helpful link https://github.com/pljson/pljson to parse json using pl/sql. When I try to do this I am getting below error. Looking at the error, I am sure the JSON List parser is looking for '[' ']' in order to identify start and end of the list. But my response starts with '{' and ends with '}'. Please help me out with this on how to parse this type of json output using the json_list. Any suggestions please. Also, in the below JSON; I only need string starting from '[' and ending with ']' highlighted the text in bold red color.
I get the below error.
ORA-20101: JSON List Parser exception - no [ start found
ORA-06512: at "myschema.JSON_PARSER", line 695
ORA-06512: at "myschema.JSON_LIST", line 17
ORA-06512: at line 54
My Process.
{code}
DECLARE
l_list json_list;
a1 VARCHAR2 (255);
a2 VARCHAR2 (255);
a3 DATE;
l_response_text CLOB
:= '{
"totalRecords" : "2",
"metadata" :
{"container":"ws:\/\/spx\/c1a22ee3","itemCounts":{"folders":0,"documents":2},"parent":{"node":"ws:\/\/SpacesStore\/c1a22ee3","permissions":{"userAccess":{"edit":false,"cancel-checkout":false,"permissions":false,"create":false,"delete":false}}},"repId":"5c98f67c7d2a","onlineEditing":true},
"items" : [ {
"relativePath" :"/fol1/fol2/test123.pptx",
"nodeRef" :"workspace://SpacesStore/66bb2e236b54",
"type" :"document",
"mimetype" :"application/vnd-presentationml.presentation",
"fileName" :"meet1 Deck.pptx",
"displayName" :"meeting 1 Deck.pptx",
"title" :"Meeting 1 Deck",
"description" :"test desc.",
"createdOn" :"2014-11-10T12:26:24.413-08:00",
"createdBy" :"Test User",
"modifiedOn" :"2014-11-10T12:26:25.806-08:00",
"modifiedBy" :"Test User",
"modifiedByUser" :"test",
"version" :"1.0",
"contentUrl" :"http://www.testurl/test.pptx",
"tags" :[],
"size" :"833",
"downloadUrl" :"http://test.com/getDoc?param=66bb2e236b54"
},
{
"relativePath" :"/test2.pptx",
"nodeRef" :"workspace://SpacesStore/5cd344e21db5",
"type" :"document",
"mimetype" :"application/123.presentation",
"fileName" :"test3.pptx",
"displayName" :"test my test3.pptx",
"title" :"Tests Flow",
"description" :"Sample test 3",
"createdOn" :"2014-11-10T12:26:24.171-08:00",
"createdBy" :"user22",
"modifiedOn" :"2016-03-01T18:22:50.779-08:00",
"modifiedBy" :"Test user 123",
"modifiedByUser" :"my name",
"version" :"1.1",
"contentUrl" :"http://test.com/1/n12/cont123/tzt.pptx",
"tags" :[],
"size" :"202",
"downloadUrl" :"http://test.com/getDoc?param=66bb2e236b5456"
}
]}';
BEGIN
l_list := json_list (l_response_text);
FOR i IN 1 .. l_list.COUNT
LOOP
a1 := json_ext.get_string (json (l_list.get (i)), 'nodeRef');
DBMS_OUTPUT.put_line (a1);
a2 := json_ext.get_string (json (l_list.get (i)), 'modifiedOn');
a2 :=
REGEXP_SUBSTR (a2,
'[^T]+',
1,
1);
DBMS_OUTPUT.put_line (a2);
a3 :=
TO_DATE (TO_CHAR (TO_DATE (a2, 'YYYY-MM-DD'), 'MM/DD/YYYY'),
'mm/dd/yyyy');
DBMS_OUTPUT.put_line (a3);
INSERT INTO my_json (a1, a2, a3)
VALUES (a1, a2, a3);
END LOOP;
COMMIT;
END;
{code}
Thanks,
RaGu