I am new to ORDS. I am using APEX_JSON to create a json object for one of my rest services with source type as PL/SQL. The json object that i am creating is complex. If any thing fails while creating the json object then I have to return an error which has different json structure in the body. So before creating the body for error I want to clear the json that I have created till this point. How can I do this. Or are there any other ways to achieve this.
PROCEDURE GETNODES(I_LOCATION IN NUMBER)
V_STATUS NUMBER;
BEGIN
APEX_JSON.OPEN_OBJECT;
APEX_JSON.OPEN_ARRAY('NODES');
FOR nodes in (select NAME FROM NODES where location = I_LOCATION) LOOP
APEX_JSON.OPEN_OBJECT;
APEX_JSON.write('NAME',nodes.name);
V_STATUS := getnodestatus(nodes.name); --This can throw an exception
APEX_JSON.write('STATUS',V_STATUS);
APEX_JSON.CLOSE_OBJECT;
END LOOP;
APEX_JSON.CLOSE_ARRAY;
APEX_JSON.CLOSE_OBJECT;
EXCEPTION WHEN OTHERS THEN
--IF I get any error, then I have to write a different json, so I have to clear the json written till now, how to do it?
APEX_JSON.OPEN_OBJECT;
APEX_JSON.write('ERROR CODE',SQLCODE);
APEX_JSON.write('ERROR MSG',SQLERRM);
APEX_JSON.CLOSE_OBJECT;
END;
PS: The above code is just for an example. The JSON object that I am writing inside the loop is much more complex with multiple levels of arrays.