Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Returning Success/failure codes from Stored procs

martin75Jan 24 2008 — edited Jan 25 2008

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;
/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 22 2008
Added on Jan 24 2008
12 comments
2,548 views