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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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,289 views