Loop through dynamic JSON file IN pl/SQL and do DML operations

I have to write a function for this app that has a grid where you can input value in rows, delete rows, update them etc.

When you do such operation, a JSON file is sent as payload and I can handle it with a procedure, but I am having problems with situations where a user wants to edit multiple rows, it updates just one of them.

This is an example of JSON file, where the description key is the one that is changed from null to the one with values (v1, v2, v3)

If value in grid is changed, it includes key "Changed" with value 1, in case of delete "Deleted" and so on.

{
    "Changes": [
        {
            "id": "AR46",
            "Changed": 1,
            "SESSION_ID": "963",
            "NAME": "IMAGE_LOGO",
            "VALUE": "",
            "DESCRIPTION": "v1",
            "TYPE": "IMAGE",
            "PARAM_GROUP": "J",
            "BLOB_VALUE": "oracle.sql.BLOB@2ba32",
            "EDIT": "Edit",
            "DOWNLOAD": "Download",
            "CLOB_VALUE": "oracle.sql.CLOB@7fd86843",
            "XML_VALUE": "",
            "CREATE_DATE": "11.03.2022 13:04:26",
            "_DefaultSort": ""
        },
        {
            "id": "AR47",
            "Changed": 1,
            "SESSION_ID": "963",
            "NAME": "IMAGE_HPB_MEMO_FOOTER",
            "VALUE": "",
            "DESCRIPTION": "v2",
            "TYPE": "IMAGE",
            "PARAM_GROUP": "JASPER",
            "BLOB_VALUE": "oracle.sql.BLOB@7621f9df",
            "EDIT": "Edit",
            "DOWNLOAD": "Download",
            "CLOB_VALUE": "oracle.sql.CLOB@43e24152",
            "XML_VALUE": "",
            "CREATE_DATE": "11.03.2022 13:04:35",
            "_DefaultSort": ""
        },
        {
            "id": "AR48",
            "Changed": 1,
            "SESSION_ID": "963",
            "NAME": "IMAGE_HPB_MEMO_INVCRED",
            "VALUE": "",
            "DESCRIPTION": "v3",
            "TYPE": "IMAGE",
            "PARAM_GROUP": "JASPER",
            "BLOB_VALUE": "oracle.sql.BLOB@762074f6",
            "EDIT": "Edit",
            "DOWNLOAD": "Download",
            "CLOB_VALUE": "oracle.sql.CLOB@4a068001",
            "XML_VALUE": "",
            "CREATE_DATE": "11.03.2022 13:04:46",
            "_DefaultSort": ""
        }
    ]
}

And this is the function that I wrote that works for just one edit/update. There aren't any errors if you try to update multiple rows, but still, just one(the first) one, is changed.

create or replace function changesResources (p_data varchar2)
return varchar2
IS
    l_nullEx    exception;
    PRAGMA EXCEPTION_INIT(l_nullEx, -1400);
    p_rez     varchar2(100);
    p_session_id number;
    p_name varchar2(100);
    p_value VARCHAR2(500);  
    p_description VARCHAR2(1000); 
    p_type VARCHAR2(100);  
    p_param_group VARCHAR2(100);  
    p_blob_value VARCHAR2(1000);           
    p_clob_value VARCHAR2(1000);           
    p_xml_value VARCHAR2(1000);    
    p_create_date varchar2(50);
    
    l_json_obj JSON_OBJECT_T;
    l_json_arr JSON_ARRAY_T;
Begin

   l_json_obj := JSON_OBJECT_T.PARSE(p_data);

   l_json_arr := l_json_obj.get_array('Changes');
   
FOR i IN 0..l_json_arr.get_size()-1 LOOP
    
     p_session_id := JSON_VALUE(l_json_arr.get(i).to_string(), '$.SESSION_ID');
   p_name := JSON_VALUE(l_json_arr.get(i).to_string(), '$.NAME');
   p_value := JSON_VALUE(l_json_arr.get(i).to_string(), '$.VALUE');
   p_description := JSON_VALUE(l_json_arr.get(i).to_string(), '$.DESCRIPTION');
   p_type := JSON_VALUE(l_json_arr.get(i).to_string(), '$.TYPE');
   p_param_group := JSON_VALUE(l_json_arr.get(i).to_string(), '$.PARAM_GROUP');
   p_blob_value := JSON_VALUE(l_json_arr.get(i).to_string(), '$.BLOB_VALUE');
   p_clob_value := JSON_VALUE(l_json_arr.get(i).to_string(), '$.CLOB_VALUE');
   p_xml_value := JSON_VALUE(l_json_arr.get(i).to_string(), '$.XML_VALUE');
   p_create_date := JSON_VALUE(l_json_arr.get(i).to_string(), '$.CREATE_DATE');

    

IF JSON_VALUE(l_json_arr.get(i).to_string(), '$.Changed') = 1
THEN

    UPDATE BF_RESOURCES_CONF
        SET description = p_description,
        value=p_value,
        type = p_type,
        param_group = p_param_group,
        blob_value = utl_raw.cast_to_raw(p_blob_value),
        clob_value = TO_CLOB(p_clob_value),
        xml_value=p_xml_value,
        create_date = TO_DATE(p_create_date,'DD.MM.YYYY HH24:MI:SS')
        where session_id = p_session_id
        and name = p_name;
    p_rez := '1|success!';
    return p_rez;
ELSIF JSON_VALUE(l_json_arr.get(i).to_string(), '$.Deleted') = 1
THEN
DELETE FROM BF_RESOURCES_CONF
WHERE session_id = p_session_id
        and name = p_name;
        p_rez := '1|success!';
    return p_rez;
ELSE
INSERT INTO BF_RESOURCES_CONF (session_id,name, value,description, type,param_group,blob_value,clob_value,xml_value,create_date) VALUES (p_session_id, p_name, p_value, p_description, p_type, p_param_group, utl_raw.cast_to_raw(p_blob_value),TO_CLOB(p_clob_value),p_xml_value,TO_DATE(p_create_date,'DD.MM.YYYY HH24:MI:SS'));
        p_rez := '1|success!';
    return p_rez;
END IF;
END LOOP;
   EXCEPTION
    WHEN l_nullEx THEN
           p_rez := '-1|Columns SESSION_ID, NAME I CREATE_DATE have to contain values!';
           RETURN p_rez;
        --WHEN OTHERS THEN
           -- p_rez := '-1|Error!';
           -- RETURN p_rez;
END changesResources ;

On 12.1 and above JSON_TABLE is available. Here is an example on the emp sample table similar to yours:

CREATE OR REPLACE function update_emp (p_data VARCHAR2)
RETURN VARCHAR2
IS
l_result VARCHAR2(1000);
BEGIN
FOR r IN (
with json_doc AS 
(SELECT p_data AS json_data FROM dual
)
SELECT 
empno,
changed,
deleted,
salary
FROM 
json_doc t,
JSON_TABLE(json_data, '$.Changes[*]'
COLUMNS (
empno  NUMBER   PATH '$.empno',
changed  NUMBER   PATH '$.Changed',
deleted  NUMBER   PATH '$.Deleted',
salary  NUMBER   PATH '$.Salary'
))
) LOOP
IF r.changed = 1 THEN
UPDATE emp SET sal = r.salary WHERE empno = r.empno;
l_result := l_result || ', updated: '||r.empno;
ELSIF r.deleted = 1 THEN
DELETE FROM emp WHERE empno =  r.empno;
l_result := l_result || ', deleted: '||r.empno;
END IF;
END LOOP;
RETURN LTRIM(l_result, ', ');
END update_emp;
/
set serveroutput on size 999999
clear screen
declare
l_data   varchar2(1000);
l_return varchar2(1000);
begin
l_data := '{
"Changes": [
{
"empno": 7698,
"Changed": 1,
"Salary": 4000,
},
{
"empno": 7788,
"Changed": 1,
"Salary": 5000,
},
{
"empno": 7876,
"Deleted": 1
}
]
}';
l_return := update_emp(p_data => l_data);
dbms_output.put_line('l_return = ' || l_return);
end;
/
l_return = updated: 7698, updated: 7788, deleted: 7876
PL/SQL procedure successfully completed.