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;
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;