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;

Capture the error into what ??? Are you looking to store the error to a table. — XING
– XING, Commented Oct 12, 2016 at 14:59
Let's try to simplify your question: you have a procedure that handles all the exceptions and uses DBMS_OUTPUT to print the error. Where is this procedure called from? an Oracle job? and what do you want to do with the error? do you want the job to raise an error? — Aleksej
– Aleksej, Commented Oct 12, 2016 at 15:12
If you only want the caller to raise a exception, simply remove the exception handling in your procedure or, if you want to print and propagate the exception, use RAISE — Aleksej
– Aleksej, Commented Oct 12, 2016 at 15:23

dvsoukup · Accepted Answer · 2016-10-12 15:31:50Z

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;

Aleksej · Accepted Answer · 2016-10-12 15:31:58Z

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.

Collectives™ on Stack Overflow

How to retrieve output parameter from stored procedure in oracle

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related