This content has been marked as final. Show 4 replies
That would depend on the implementation of your procedure. I would say if there is no exception thrown then the procedure completed successfully. Of course there might be some additional conditions when you consider your procedure run successful.
So in the end you have to design your procedure so the caller can recognize if the procedure run successful or not. How you want to do this is up to you; you can use user defined exceptions (raise_application_error) if an error in your logic occurs which is not a default oracle exceptions, functions which return booleans or output stati.
I for one prefer exceptions, as they force the caller to handle them (I add the user-defined exceptions thrown by each procedure in the specification right to the documentation so the one using my procedure sees it right away) and ensure my procedure is atomic.
But as said this is entirely up to you.
The success message in SQL*Plus/Toad/SQL Developper/whatever is shown when no exceptions are thrown by your procedure. Basically this would be something like
However as said depending on the implementation of your procedure this is correct or not.
begin your_procedure; message('Procedure Completed'); exception when others then message('Procedure run with errors: '||sqlerrm); end;
If you really want to know the outcome of your database procedure, you could do one of three things.
First, use defined exceptions (as Christian suggested) or you could convert the procedure to a function that returns a success or failure value or returns the exception. The other option would be to add and OUT parameter to your procedure that basically does the same thing; returns a success or failure value or the exception thrown.