plsqloracle-apex

`apex_json.get_VARCHAR2(p_path=>'w',p_values=>apex_json.g_values)` function return null


I have situation of building json using apex_json as the code sample explain it. the problem is that apex_json.get_varchar2('w') return null, even after I checked the apex_json.g_values. any one could give me any hint about it?

the code:

declare
    v_var varchar2(222);
    begin
        apex_json.initialize_clob_output;
        APEX_JSON.open_object;
        APEX_JSON.write('w', 'no errors');
        APEX_JSON.close_object;
        V_VAR := nvl(apex_json.get_VARCHAR2(p_path=>'w',p_values=>apex_json.g_values), '--');
        DBMS_OUTPUT.PUT_LINE('w value is: '|| V_VAR);
        DBMS_OUTPUT.PUT_LINE(apex_json.get_clob_output( p_free => true ));
    end;

the output:

w value is: --
{
"w":"no errors"
}

Solution

  • You can't do that in a single step. Write the json, then parse it again instead:

    DECLARE
        l_json_val CLOB;
    BEGIN
        apex_json.initialize_clob_output;
        apex_json.open_object;
        apex_json.write(
                       'w'
                      ,'no errors'
        );
        apex_json.close_object;
        l_json_val := apex_json.get_clob_output(p_free => TRUE);
        dbms_output.put_line(l_json_val);
        
        APEX_JSON.parse(l_json_val);
        dbms_output.put_line(nvl(apex_json.get_VARCHAR2(p_path=>'w',p_values=>apex_json.g_values), '--'));
    END;
    /
    
    {
    "w":"no errors"
    }
    
    no errors
    
    
    PL/SQL procedure successfully completed.