I want to execute an oracle database procedure in oracle forms environment and show the result of execution to user(as "procedure executed successfully".. etc) How can I achieve this ?
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.
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.