ora-1722 errors after upgrade to 8.1.7.1.3
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: ora-1722 errors after upgrade to 8.1.7.1.3

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    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.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width