-
We have upgraded our database from 7.3.4.4.0 to 8.1.7.1.3 and we are now getting ORA-1722: invalid number errors.
In the failed sql statements, we are using comparison of different datatypes, e.g. where varchar2='number' (contract_id='10').
We have found that we don't get this error if we either:
1. Use the Rule based optimizer. or
2. Put to_char round the number,
i.e. varchar2=to_char(number) (contract_id=to_char(10).
We have also found that the CBO was causing very poor performance. Even though I had set OPTIMIZER_INDEX_CACHING=99 and OPTIMIZER_INDEX_COST_ADJ=10 to force CBO to use indexes.
For a workaround we have reverted back to the Rule based optimizer but would eventually like to use CBO due to partitioning and bitmap indexes. Has anyone come accross this before? Does anyone have a solution?
I know I could use to_char then use function indexes but this means amending all our code.
-
Implicit data conversion will NOT always work properly neither in 7.3.4 nor in 8.1.7.
Basically you need to check/modify your application code.
