Oracle-L: Re: imported database inconsistencies

From: <Jared.Still_at_radisys.com>
Date: Thu, 22 May 2003 14:18:06 -0800
Message-ID: <F001.005A0D71.20030522141806@fatcity.com>

Steve,

Possibly an exp bug. See MetaLink doc 223399.1

Jared

"Steve McClure" <smcclure_at_usscript.com>
Sent by: root_at_fatcity.com
05/22/2003 02:08 PM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        imported database inconsistencies

List,

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.

Steve

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Steve McClure
INET: smcclure_at_usscript.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author:
INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------