How to clear the json object written by APEX_JSON

Viewed 3924

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.

1 Answers
Related