Hi,
I am new to PL/SQL and i need to parse this JSON data. I have inserted it into my table in Oracle database 19c.
JSON data: {"userId":"generic-employee_id","privileges":[{"name":"V ","code":"A"},{"name":"F","code":"M"},{"name":"O","code":"C"},{"name":"S","code":"E"},{"name":"P","code":"B"},{"name":"W","code":"SW"}]
So i wrote this code-:
SET SERVEROUT ON FORMAT WRAPPED LINESIZE 100
DECLARE
l_clob CLOB;
l_top_obj JSON_OBJECT_T;
l_priv_arr JSON_ARRAY_T;
l_priv_obj JSON_OBJECT_T;
BEGIN
SELECT data
INTO l_clob
FROM json_documents
WHERE id = 1;
l_top_obj := JSON_OBJECT_T(l_clob);
l_priv_arr := l_top_obj.get_array('privileges');
FOR j IN 0 .. l_priv_arr.get_size - 1 LOOP
l_priv_obj := TREAT(l_priv_arr.get(j) AS JSON_OBJECT_T);
DBMS_OUTPUT.put_line(' --');
DBMS_OUTPUT.put_line(' index : ' || j);
DBMS_OUTPUT.put_line(' name : ' || l_priv_obj.get_string('name'));
DBMS_OUTPUT.put_line(' code : ' || l_priv_obj.get_string('code'));
END LOOP;
END;
/
But it gives me an error: ERROR at line 1:
ORA-40441: JSON syntax error
ORA-06512: at "SYS.JDOM_T", line 9
ORA-06512: at "SYS.JSON_OBJECT_T", line 43
ORA-06512: at line 13
Please help i am new to PL/SQL and i am not able to debug this.
Warm Regards,
Shubham Joshi