I am working on fixing a large query that I inherited. The query consists of over 200 lines of DML statements.
The search queries the database restricting the data to a single month. Most months work just fine, but there are a few months that generate an
ORA-01722: Invalid Number error.
I suspect the problem is one of the following:
- a column defined as VARCHAR contains alpha characters but is being used where a number is required
- a column is null and is being used where a number is required
If I can find the row that is causing the problem, I can change the query to handle said data appropriately. However, I don't know how I can isolate that row. I have attempted to break the query into parts and see where null values are being generated, but the entire query is dependent on a number of outer joins. I have been able to isolate the range to few days, but that doesn't allow me to isolate the row in question.
Also, the query has been defined as a stored procedure. At the end of the procedure I have defined an error handler to catch all errors and display a message:
EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001, error_message || parameter_string_values, TRUE);
However, the procedure raises the
Invalid Number error and does not get trapped by the error handler at the bottom.
- Are there any methods or database variables that I can set to help isolate this error?
- Why isn't the
ORA-01722: Invalid Numbererror being trapped by the error handler?