Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> imported database inconsistencies

imported database inconsistencies

From: Steve McClure <>
Date: Thu, 22 May 2003 13:08:15 -0800
Message-ID: <>


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))

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

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.



Please see the official ORACLE-L FAQ:

Author: Steve McClure

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message to: (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). Received on Thu May 22 2003 - 16:08:15 CDT

Original text of this message