I have a test instance of our database that I create every so often by
exporting our production instance, and recreating the database and on a
different server. Well now that test instance is actually seeing some
usage, and a couple of forms that work properly on the production instace,
but produce errors on the test instance. The errors are actually invalid
number errors. I can actually duplicate the error by executing queries in
sqlplus. The same query on the same tables, actually very static
tables(same number of rows on each instance). I don't have date time stamps
on the table in question, so I can't verify that no one has updated the
production instance...but well the tables are SO static that really is
unlikely. I have compared the rows the query should return on both
instances, and they are identical.

Again the same query causes an error in one instance, but not the other.
The query is fairly straight forward.

Select *
from ref_codes
where to_number('1') IN (to_number(RV_LOW_VALUE),
to_number(RV_ABBREVIATION))
and RV_DOMAIN ='PRICING_CD'

The Error is
WHERE ( to_number('1') IN (to_number(RV_LOW_VALUE) .....

<---error in to_number(RV_LOW_VALUE)
ORA-01722 invalid number

I have doublechecked the character set of both databases match. I have
verified that the RV_LOW_VALUE columns contain only numeric characters for
the columns that should be returned. I did this by selecting
'x'||RV_LOW_VALUE||'x', and verifying that there were no special characters
or spaces returned in the query.

The only difference I am aware of between the two instances is that one has
OPTIMIZER_MODE set to "CHOOSE"(test instance where error is occuring), and
the other is set to "RULE"(production queries function as expected).

Anyone have an idea where to look next. I am going to bounce the test
instance, and change optimizer mode shortly. I just don't know what to look
for after that.

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).