This discussion is archived
6 Replies Latest reply: Dec 2, 2012 11:11 PM by 976640 RSS

SQLException: List of error code for oracle?

976640 Newbie
Currently Being Moderated
Hello There,

I am trying to handle SQLExceptions. The database used is Oracle 11g.
The purpose is to show user friendly messages based on the error codes.
To do this, i can find one way to go for error codes.
In the catch i will write switch statement having cases for different error codes and i will show
user friendly messages based on that.

The problem is, i did not found the list of error codes for oracle.
Can anybody please help on this.

Thanks,
Arfeen.
  • 1. Re: SQLException: List of error code for oracle?
    gimbal2 Guru
    Currently Being Moderated
    973637 wrote:
    The problem is, i did not found the list of error codes for oracle.
    Can anybody please help on this.
    http://lmgtfy.com/?q=list+of+ora+errors
  • 2. Re: SQLException: List of error code for oracle?
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!
    >
    I am trying to handle SQLExceptions. The database used is Oracle 11g.
    The purpose is to show user friendly messages based on the error codes.
    To do this, i can find one way to go for error codes.
    In the catch i will write switch statement having cases for different error codes and i will show
    user friendly messages based on that.
    >
    What do you plan to do with the actual SQLException? I hope you plan on logging it. The actual error code and message is what is needed if anyone has to try to determine exactly what caused the error. Your 'user friendly' message will be useless for that purpose.

    Why do you want to expose your users to ANY sql-based exceptions? They won't be able to do anything about it except call for tech support. And tech support won't be able to do anything about it if they don't have the actual error code and message.

    Any error message displayed to the user needs to 1) be useful in guiding the user as to what to do to recover or avoid the problem and 2) be useful to tech support if the user needs to ask for help.

    You couldn't possibly do this for all exceptions since they number in the thousands. Your 'switch' statement would quickly become your applications biggest performance problem when any of those exceptions occurred. And I assume you do know that the exceptions can be different for different Oracle versions, although the most common ones are the same.

    The exceptions are mostly (there are some missing listed in the ERROR MESSAGES doc
    http://docs.oracle.com/cd/B28359_01/server.111/b28278/toc.htm
  • 3. Re: SQLException: List of error code for oracle?
    976640 Newbie
    Currently Being Moderated
    Thank you rp0428.

    Actually the work is at data level. So it the tool used by business team to push data into database.
    So whatever the data they are pushing, they need log messages.
    Business people are not much technical (obviously) so they need such messages which can be helpful to correct the data at their own.
    That is the objective

    Hope i am clear.
    Thanks,
    Arfeen.
  • 4. Re: SQLException: List of error code for oracle?
    Kayaman Guru
    Currently Being Moderated
    973637 wrote:
    Business people are not much technical (obviously) so they need such messages which can be helpful to correct the data at their own.
    That is the objective
    Sorry to say, but you'll never succeed in that.
  • 5. Re: SQLException: List of error code for oracle?
    rp0428 Guru
    Currently Being Moderated
    >
    Actually the work is at data level. So it the tool used by business team to push data into database.
    So whatever the data they are pushing, they need log messages.
    Business people are not much technical (obviously) so they need such messages which can be helpful to correct the data at their own.
    That is the objective
    >
    Thanks for the explanation. That helps explain what you are trying to do.

    I'm not trying to discourage your effort but just trying to make sure you know what you are getting into and that you plan accordingly. I've been on the 'technical' side of trying to help similar business users for 30 years.

    The main issue is that because the business people are not technical they can't be much help in explaining what the problem really is. Much of the time they can't 1) explain exactly what they were doing that caused the problem, 2) repeat the exact steps that cause the problem or 3) reproduce the problem. They also typically don't have direct access to the tables or data involved so can't see if there is a duplicate record or even know how to check for a duplicate record or missing 'parent' record.

    That means the technical person (i.e. YOU) has to try to find the actual problem based on the 'sanitized' error message you are now giving the user. If you don't have access to the actual error that Oracle reported it will be very difficult to track down a lot of the problems.

    The first step in designing ANYTHING is to gather and document the requirements. For your example those requirements include:
    1. An exact list of Oracle exceptions that need to be trapped - that task alone will be challenging
    2. The list of 'sanitized' business language to be used for each exception to display to the user
    3. How to log the ACTUAL Oracle exception and information so that it is available for the technical personnel.
    4. What to actuall do in the application when each exception occurs. Some exceptions may require just a warning that something happened, some may require the user to take some specific action and some may require an immediate '911' call to tech support.
    5. How to test each of those exceptions to make sure they are handled properly and that the information logged and provided to the user is sufficient to actually find and deal with the problem.

    I suggest you start with #5 and work with your co-workers to try to develop a process that you think will actually work if it did exist.

    1. Identify one or two Oracle exceptions that you need to deal with. Select ones that have actually occured if possible.
    2. Create a test case that actually causes the exception to occur.
    3. Using ONLY the message you display to the user see if another developer (one not associated with your project) can determine what the problem actually is and how to correct it. The ability of that 3rd person to find and fix the problem will be a good indicator as to whether your plan will work or what information is missing.
    4. Now using both the user message and the actual Oracle Exception see if that person can find and fix the problem.

    If you actually do the above walk-through for a couple of exceptions you will get a good idea of just what the issues are in dealing with them and be better able to design something that will actually help solve the problem.

    Once simple case is a user that tries to create a child record when the parent record doesn't exist. For example you have a parent table 'CUSTOMER' and a child table 'ADDRESS. The CUSTOMER table primary key is CUSTOMER_ID (generated by a sequence generator) and the ADDRESS table primary key is CUSTOMER_ID and ADDRESS_SEQ (generated by a sequence generator). There is a foreign key from the ADDRESS table to the CUSTOMER table using the CUSTOMER_ID.

    See what happens if you try to INSERT a new address record for a customer that doesn't exist. Oracle will throw a constraint exception. What message will you display to the user? Perhaps 'CUSTOMER record does not exist'? There could be more than one foreign key on that ADDRESS table. How will you tell if it if the FK to the customer table or the FK to a different table?

    At a minimum you need to actually conduct a series of walk-throughs to see how your proposed system might work and to make sure that you collect the information you need for both the business user AND the technical user.

    It's appropriate to display an easy to read message to the business user. Just make sure that, behind the scenes, you collect the ACTUAL information that the technical people need to help the business user deal with the problem.
  • 6. Re: SQLException: List of error code for oracle?
    976640 Newbie
    Currently Being Moderated
    Thank you very much for discussing this issue in depth.
    It was really helpful.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points