How to retrieve output parameter from stored procedure in oracle

Question

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.

    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 > 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 <> '0'
        THEN
           OUT_MESSAGE := LRETVALUE;
            RETURN;
        END IF;
    END IF;

EXCEPTION
WHEN OTHERS
THEN DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE' || SQLERRM);

END COMP_JSON;

Answer 1

If you need the caller to raise an exception, you need that your procedure propagates it to the external. For example:

SQL> create or replace procedure raiseException(p in number) is
  2      n number;
  3  begin
  4      n := p/0;
  5  exception
  6      when others then
  7          dbms_output.put_line('Error message: ' || sqlerrm);
  8          raise;
  9  end;
 10  /

Procedure created.

SQL> create or replace procedure procedureCall is
  2  begin
  3      raiseException(10);
  4  end;
  5  /

Procedure created.

The first procedure will print a message and raise an exception; in this way, we have the error message in output and an exception:

SQL> exec procedureCall
Error message: ORA-01476: divisor is equal to zero
BEGIN procedureCall; END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "ALEK.RAISEEXCEPTION", line 8
ORA-06512: at "ALEK.PROCEDURECALL", line 3
ORA-06512: at line 1

If you remove the RAISE, the exception will be handled and not propagated, thus giving no error:

SQL> create or replace procedure raiseException(p in number) is
  2      n number;
  3  begin
  4      n := p/0;
  5  exception
  6      when others then
  7          dbms_output.put_line('Error message: ' || sqlerrm);
  8  end;
  9  /

Procedure created.

SQL> exec procedureCall
Error message: ORA-01476: divisor is equal to zero

PL/SQL procedure successfully completed.

Answer 2

You're using a nested block to throw your exception, but it will continue processing. The outbound variable "OUT_MESSAGE" should capture that value. Is it? if so, you can see what it is with this:

SQL> VAR ERR_MSG VARCHAR2;
SQL> EXEC COMP_JSON(:ERR_MSG, 5);  --whatever you use for PNI_ID....

PL/SQL procedure successfully completed.

SQL> PRINT ERR_MSG;

If your program never throws an error, then OUT_MESSAGE will never be set, thus it will be null upon completion of the program.

If you are looking to throw an error from your program if your "nested block" throws an error, then you need to re-raise the exception so that the outer exception catches it:

               IF K > 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 || ']';
            RAISE_APPLICATION_ERROR(-20000, LRETVALUE );
    END;