I have a stored proc which is basically a caller stored proc ie. Execute/Call several other procedures in the same package in a particular order.
A return code(an OUT parameter in caller proc) is used to check if all the procedures are succefully executed.
For Success the p_ReturnCode=1
For failure the p_ReturnCode=0
To achieve this i placed p_ReturnCode:=1 bit at the end of the final procedure being called and p_ReturnCode:=0 (failure) on the exception bit. Is this the right way to return success/failure codes?
This is how my caller stored proc will look like
PROCEDURE package_name.CALLERPROCEDURE(INPARAMETER1 IN VARCHAR2,
INPARAMETER2 IN VARCHAR2,
.
.
P_RETURNCODE OUT NUMBER)
IS
.
.
--EXECUTE FIRST PROC
PROCEDURE1();
--EXECUTE SECOND PROC
PROCEDURE2();
--EXECUTE THIRD PROC
PROCEDURE3(); ----final procedure
--COMMITTING.
COMMIT;
P_RETURNCODE:=1:
EXCEPTION .....
......
P_RETURNCODE:=0;
END PACKAGE_NAME;
/