Getting ORA-01722: invalid number in MarkView SQL*Flow Manager Log file, or other system component.
This behavior can be caused if changing from Rule- to Cost-Based Optimization, whether through a change of the optimizer_mode database parameter, or by gathering statistics using Analyze or the DBMS_Stats package.
This is particularly important to check when upgrading to a DB version which requires CBO - and thus gathering statistics - e.g. 9i.
The problem arises from a query with a join condition that compares a varchar column with a number column. Most often, the problem occurs when joining ap_invoices (ap_invoices_all) with sf_workitem_property_value (sf_workitem_property_values_v):
... from sf_workitem_property_values_v wpv, ap_invoices api where api.invoice_id = wpv.property_value and wpv.property_name = 'InvoiceID';
AP_Invoices.Invoice_ID is a Number(15)
SF_Workitem_Property_Value.Property_Value is a Varchar2(2000)
The Rule-Based Optimizer obtains the correct optimal plan by using an INDEX RANGE SCAN on SF_WORKITEM_PROPERTY_VALUE, using the SF_WPV_WI_CLASS_PROPERTY_ID_FK index. This index will restrict the rows in the comparison to workitem properties that are for 'InvoiceID' (which are numbers, as enforced by the MarkView Application System).
The Cost-Based Optimizer can sometimes determine that a TABLE ACCESS FULL of SF_WORKITEM_PROPERTY_VALUE will be faster than an INDEX RANGE SCAN. This plan will require that every row be evaluated in the comparison:
where api.invoice_id = wpv.property_value
When evaluating the rows, because the join condition has no explicit conversion, the database engine will perform an implicit conversion on the right hand side of the equation (wpv.property_value). However, because some of the rows contain character (non-numeric) data, (such as 'JSMITH'), these values cannot be converted to numbers, and so the 'ORA-01722: invalid number' exception is raised.
One short-term solution may be to revert to the Rule-Based Optimizer, by deleting MarkView schema statistics. However, as we have seen with other issues, the right thing is to run the database with Cost-Based Optimization. Therefore, the correct long-term solution is to change the SQL to properly restrict the returned values to numeric only, or to explicitly convert the numeric side to character (in this case, to_char(api.invoice_id) = wpv.property_value).
It may also be possible to adjust the database parameters to achieve the same goal of using the appropriate index to restrict the rows evaluated in the comparison. The optimizer_index_cost_adj parameter adjusts the perceived cost of accessing the index in comparison to a table scan.
The default value of 100 means that the Cost-Based Optimizer (CBO) will assume that an index scan will cost just as much as table full scan, which will result in the CBO favoring a TABLE ACCESS FULL over the INDEX RANGE SCAN. By adjusting this parameter down, (as is generally recommended for OLTP systems by Oracle database tuning experts), to perhaps 20, it may be possible to have the CBO determine an access plan that uses the appropriate INDEX RANGE SCAN.
Keywords: invalid, stats, statistics, 9, 9i, 8, 8i, database, db, ora, 01722, gather, schema, number