title: How to retrieve output parameter from stored procedure in oracle
body: <p>I have below stored procedure. It works fine. But I wanted to test it for error scenarios. Even when there is an error, the procedure executes successfully without showing the error message. When I enable the set serveroutput on, it shows the error message. But I want to capture the error message.</p>
  
  <pre><code>    create or replace PROCEDURE         COMP_JSON (
      OUT_MESSAGE                 OUT  VARCHAR2,
      PNI_ID                      IN   NUMBER
  )
   AS
  
      CURSOR C1 IS SELECT 1  AS ID,TYPE_ID, COMP, TYPE, PREV_AMOUNT, CURR_AMOUNT FROM V_COMP_COST;
  
      SECID VARCHAR2(100);    
  
      K NUMBER:= 0;
      L NUMBER:= 1000;--Commit Interval
  
      LRETVALUE VARCHAR2(200):='0';
  
      V_TYPE_ID      JSON_DATA.TYPE_ID%TYPE;
      V_COMP         JSON_DATA.COMP%TYPE; 
      V_TYPE         JSON_DATA.TYPE%TYPE;  
  
  
  BEGIN
  
      APEX_JSON.INITIALIZE_CLOB_OUTPUT;
  
  /* Cost Comparison */
      IF NVL(PNI_ID, 1) = 1
      THEN
  
          K := 0;
  
          BEGIN
              FOR I IN C1 
              LOOP 
                    V_TYPE_ID := I.TYPE_ID;
                    V_COMP := I.COMP; 
                    V_TYPE := I.TYPE;  
  
                    APEX_JSON.OPEN_OBJECT;
                    APEX_JSON.WRITE('prevAmt',I.PREV_AMOUNT);      
                    APEX_JSON.WRITE('currAmt',I.CURR_AMOUNT);      
                    APEX_JSON.CLOSE_OBJECT;
  
                    INSERT INTO JSON_DATA 
                    VALUES (I.ID,I.TYPE_ID,I.COMP,I.TYPE,APEX_JSON.GET_CLOB_OUTPUT);      
                    /* Commit Interval  */
                    K := K+1;
  
                    IF MOD(K,L) = 0 
                    THEN 
                        COMMIT;
                    END IF;
  
                    APEX_JSON.FREE_OUTPUT;
  
                    IF K &gt; 5
                    THEN 
                        RAISE_APPLICATION_ERROR(-20000, NULL);
                    END IF;
              END LOOP;
  
          EXCEPTION
              WHEN OTHERS 
              THEN LRETVALUE := '-1,k:Problem in loading Data -' || SQLERRM  || ' AT: [' || V_TYPE_ID || '] [' || V_COMP || '] [' || V_TYPE || ']';
          END;
  
          COMMIT;
  
          IF LRETVALUE &lt;&gt; '0'
          THEN
             OUT_MESSAGE := LRETVALUE;
              RETURN;
          END IF;
      END IF;
  
  EXCEPTION
  WHEN OTHERS
  THEN DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE' || SQLERRM);
  
  END COMP_JSON;
  </code></pre>