Meaningful or Cuddly

Question:

What are your thoughts about how much information should be displayed to application users when an exception occurs? Say a record can't be created because of a primary key constraint—should users see the "ORA-00001...," so they can give a meaningful message to Support to help track the problem down, or should all that stuff be hidden, so the user gets a more "cuddly" message, maybe with an error log number pointing to a record in an error log table?

Answer:

This question was answered in the March/April 2006 issue of Oracle Magazine.
My feeling is that when an error occurs, you should provide a message to the user that doesn't violate your company's security protocols, is meaningful, and does not intimidate, in that order of priority.
First, let's address the issue of security. When an error occurs in your application or underlying database that you can anticipate and for which you can write explicit handler logic, you can both control the message and tailor it to user needs and the application's business model.
If, on the other hand, that error is unexpected, it is quite difficult to know a priori (that is, when you are writing generic error-handling and -reporting code) how much information should be shown. What if the error is caused by a malicious user who is attempting to hack into your Web-based application that makes heavy use of dynamic SQL? You don't really want to give any information about the state of the database, such as
ORA-1653 unable to extend table
secure_app.personal_info in tablespace sensitive_data....
And you don't want to display "bad" SQL statements—statements made bad by SQL injection attempts. You could easily reveal enormous amounts of sensitive information.
So for exceptions that are not handled in the PL/SQL code—errors that cannot be anticipated as you write your programs—it is best to keep feedback to an absolute minimum, probably something along these lines:

An unexpected error has occurred. Please contact Support and mention the problem identifier 12345.
Legitimate users will follow up; malicious hackers will look for softer targets.
Let's now address a situation in which your code has handled an error that was anticipated and likely caused by user input. Simply displaying the Oracle error message or, worse, allowing the exception to go unhandled, resulting in a display of the error stack, will not do. So, yes, you should "translate" the generic ORA-0001 or ORA-1403 information and the like into more-cuddly communications that achieve the following objectives:
• Notify the user that an error has occurred. You surely do not want to "swallow" errors.
• The error message should explain the problem in terms the user can understand. From a security standpoint, you should not display the Oracle error code or message. Instead, display a message that explains in application-specific terms what has happened. This usually entails providing context-specific values, such as the name of the column that was being set to NULL or was involved in the duplicate index error.
• Where appropriate, tell the user what to do to address the problem (if a "retry" makes sense). You might say, "Change the name of the account to a value that is not currently being used in the database" or "Adjust the birthday of the employee so that the employee is at least 18 years old."
• Give the user all the information needed to help the application-support team investigate and resolve the problem. The Oracle exception has only two attributes: error code and error message. Usually, when an error occurs in an application, there are lots of different pieces of data that both contributed to the error's occurrence and can explain the current execution context. If that information is not extracted and saved at the time of the exception, it will be much harder to fix the problem.
For example, distinguish between an error such as ORA-1403 (no data found) and the particular instance of an error ("No company located with ID = 1504"). Just as each error has its own unique identifier, each instance should also be uniquely identifiable.
When an error is encountered, gather as much information as possible about the application and system context (by calling, for example, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE) and store it with the unique identifier. Provide this instance identifier to the user, and that person can communicate it back to Support. Support then has access to a rich array of information, which it can use to fix the problem.
I have implemented many of the elements of this approach in Qnxo, a product that helps PL/SQL developers generate, reuse, and test their code more effectively. If you download and install Qnxo, you can take a look at the implementation.