11 Replies Latest reply on Oct 16, 2019 8:26 AM by Jim Smith

    No data found using apex_json in SQL Developer

    Jose Aróstegui

      Hi expert,

       

      I have a PLSQL to build Json error messages like this, and it's working perfectly in the ORDS service we have built:

      This is the code:

        --------------------------------------------------------------
        -- Function that returns a JSON Payload
        --------------------------------------------------------------
        PROCEDURE error_response(p_lookup_type IN VARCHAR2,
                                 p_error_code  IN VARCHAR2,
                                 p_error_text  IN VARCHAR2 DEFAULT NULL) IS
          l_error_code apexc_lookup_values.return_value%TYPE;
          l_error_text VARCHAR2(4000);
        BEGIN
          apex_json.open_object;
          apex_json.open_object('Status');
          l_error_code := p_error_code;
          l_error_text := p_error_text;
        
          BEGIN
            -- Query for the text related with the error message
            SELECT lv.display_value
              INTO l_error_text
              FROM apexc_lookups       l
                  ,apexc_lookup_values lv
             WHERE l.lookup_type = p_lookup_type
               AND l.lookup_id = lv.lookup_id
               AND lv.return_value = l_error_code;
          
          EXCEPTION
            WHEN OTHERS THEN
              -- If not found, return Unexpected Error (-1)
              l_error_code := '-1';
              IF l_error_text IS NULL THEN
                l_error_text := 'Unexpected error. Please contact with System Administrator.';
              END IF;
          END;
        
          apex_json.write('StatusCode', nvl(l_error_code, 0));
          apex_json.write('StatusMessage', nvl(l_error_text, 0));
          apex_json.close_object;    
          apex_json.close_object;       
        END error_response;
      

       

       

      But when we run it from SQL Developer we get this error:

      Is there any way to capture HTP output directly in SQL Developer?

       

      Thanks,
      Jose.