1

I've the following situation:

I've a view where there's a row that somehow generates incorrect data and when it's attempted to be visualized or exported it shows an error message like this:

  1. 00000 - "invalid number"
    Cause: The specified number was invalid.
    Action: Specify a valid number.

Supposedly there shouldn't be data that causes the view to have an error like that one, anyway, as it's test data, I can do well by removing all data from database tables that cause that error in the row of the view.

Problem is that before the row with the error gets to be visualized in any way it displays that message and stops showing rows.

Worst case scenario I could make a complete wipe of all the data that has to do with that view and then I could insert it again, controlling that none of the inserted data causes an error like that in the view, but it would be preferable not to have to do so, as it's a good amount of test data to be inserted again.

Any idea how could I visualize that failing row and detect what's causing the problem?

I'm using Oracle SQL developer to see the data.

3
  • What is the DDL for the view, and for the table(s) that contain the data for the view? It's hard to speculate or provide guidance when you haven't really provided any details. You should be able to identify the problem data without having to delete all of it.
    – pmdba
    Mar 30, 2023 at 11:13
  • @pmdba, the DDL is an humungous sentence that has a lot of dependant tables, having those tables almost all of its data valid, so that's not helping much. My ideal answer would be something like "open the file XXX with a text editor and you can read the data this way", maybe there's some tool for Oracle SQL developer that forces the export of a view, regardless it has errors or not, to that file XXX and allows to check how that failing data would be. Mar 30, 2023 at 11:23
  • @user2638180 a view is just a query, so you can't open any file and see the results if the query fails, and I don't see how SQL Developer can do anything about it unless it can highlight potential problems in the source code of the view. You might look at the on conversion error clause of to_date and other conversion functions, in case that helps. Apr 11, 2023 at 22:13

2 Answers 2

2

Here are some steps you can try:

(1) Check for any explicit conversions in the view code (TO_NUMBER(varcharcol))

(2) Check for any implicit conversions in the view code (5 + varcharcol)

(3) If the view code is simple, look at the joins. If it's complex, you can generate an execution plan (explain plan for select * from view) and check it out (select * from dbms_xplan.display(display=>'ALL')). Look at the join clauses and see if you see any strange functions wrapped around the columns in the predicate and filter information. You're looking for an indication that Oracle may be implicitly doing a datatype conversion in order to join two columns of different datatypes. It's not uncommon to see someone join a number column to a varchar2 column that usually contains numbers (but may not always, forever!).

(4) If you find where the conversion is happening, write a function to convert safely without failing:

CREATE OR REPLACE FUNCTION f_safe_to_number(in_value IN varchar2)
  RETURN number
AS
  var_result number;
BEGIN
  var_result := TO_NUMBER(in_value);
EXCEPTION
  WHEN OTHERS THEN
    RETURN -9999999;
END;

Then query the column(s) you identified above as undergoing numeric conversion and filter on the output of the function looking for value -9999999. Those failed the conversion.

0

The error code 1722 means you are trying to convert a string into number and that string is not a "1234" but something like a "abcd".

So, the most straight forward way to find the offending record is to look inside the VIEW definition. Find all possible places where you have conversion from varchar into numeric (be it in the resultset or in WHERE clause) and run for all involved tables and fields a query like:

select primary_key, fld
from table
where not isnumber(fld)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.