Return Message of Error code in Oracle Stored Proc

Question

The below procedure (in Oracle 11g release 1) accepts a sql as a parameter & returns its return code.

Returns 0 if success

Returns 1 if no update or no delete performs

Returns actual error code in case of failure.

How can I change below procedure to return me another out param say "return_message" which will contain short description of oracle internal error message? In case of success, it should say "success" and in case no delete/updates performed, it should say "nochange"

CREATE OR REPLACE PROCEDURE "demo"."run_demo"(v_sql IN VARCHAR2, return_code OUT number)
AS

i number;

BEGIN
   return_code := 0;
   execute immediate v_sql;
   i := sql%rowcount;

   IF (i<1)
     THEN return_code := 1;
   END IF;

EXCEPTION  
   WHEN OTHERS THEN
   return_code := SQLCODE;
END;

Daniel Haviv · Accepted Answer · 2011-12-22 02:27:24Z

9

You want to use the SQLERRM function.

Here's your code:

CREATE OR REPLACE PROCEDURE "demo"."run_demo"(v_sql IN VARCHAR2, return_code OUT number, return_message out varchar2)
AS

i number;

BEGIN
   return_code := 0;
   execute immediate v_sql;
   i := sql%rowcount;

   IF (i<1)
     THEN return_code := 1;
   END IF;

EXCEPTION  
   WHEN OTHERS THEN
   return_message := SQLERRM;
   return_code := SQLCODE;
END;

answered Dec 22, 2011 at 2:27

Daniel Haviv

1,0361 gold badge8 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Doyle · Accepted Answer · 2011-12-22 02:31:02Z

3

You can try using SQLERRM to return the message. E.g.,

create or replace procedure RUN_DEMO(V_SQL in varchar2, RETURN_CODE out number, RETURN_MSG out varchar2) as
  I                                number;
begin
  RETURN_CODE := 0;

  execute immediate V_SQL;

  I := sql%rowcount;

  if (I < 1) then
    RETURN_CODE := 1;
  end if;
exception
  when others then
    RETURN_CODE := sqlcode;
    RETURN_MSG := sqlerrm;
end;

answered Dec 22, 2011 at 2:31

John Doyle

7,8235 gold badges35 silver badges40 bronze badges

1 Comment

John Doyle Over a year ago

Ignore this, Daniel beat me to it by a few minutes.

Collectives™ on Stack Overflow

Return Message of Error code in Oracle Stored Proc

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related