Yes, I don't think there is one "good practice" rule for where to use RAE or anything else.
I certainly don't think you have to have a single exception block at the foot of every procedure, and it can do more harm than good by obscuring the original exception details. (Try supporting an application full of WHEN OTHERS THEN RAISE exception hiders - or worse still, WHEN OTHERS THEN DBMS_OUTPUT - and you'll see what I mean.)
Some have argued that using a plain RAE without any wrapper or table of error codes etc is a bad idea, but I think only because the hardcoded error codes (-20001 etc) tend to be meaningless, and you miss an opportunity to capture details in a log. However if you don't care about the codes, don't need multilingual messages and capture exception details higher up the call stack then I don't see a problem.
So is it a good practice to use RAISE_APPLICATION_ERROR () outside the exception block.
With so many different opinions i have lost the plot.
Good practices are always debatable. RAISE_APPLICATION_ERROR basically breaks the flow of your program. That's one reason I don't prefer using it outside of EXCEPTION block. But there could be valid reason for using it in the body as well. So its just for you to decide based on the knowledge (Your organization standards, project standards and your specific requirement) you posses and proceed accordingly.
I think most of us have a common understanding how we would deal with errors. The implementations might differ slightly, but the basics are all there already.
I think error raising/reraising, error handling and error logging are three things that need to be considered together! Do it, but don't overdo it!
I see, now you still use raise_appliction_error to pass your application context.
But this is not developer friendly:
I prefer it written this way:
raise_appliction_error(namederrors.c_wrongInterval,'Parameter [p_input] is not within defined interval. Expected [30-60], actual value is ['||p_input||']');
And leave the message parsing/language translation to the client.
Both alternatives require the developer to know the number and position of the placeholders. Our version needs less typing. When a developer thinks that the short version is hard to read then it is even possible to include the text since the pipe delimiter marks the variables. Yet the naming of the exception should be clear anyway.
,'"Parameter ~ is not within defined interval. Expected ~, is ~.'||
is equivalent to