RE: imported database inconsistencies

Steve,

I had a similar problem when creating a clone for a 8.1.7.4 instance.

After restoring a prod instance on a test box, one particular query started giving wrong results.
I had forgotten to patch the test database install to 8.1.7.4

-Mandar

> -----Original Message-----
> From: Steve McClure [mailto:smcclure_at_usscript.com]
> Sent: Thursday, May 22, 2003 2:08 PM
> To: Multiple recipients of list ORACLE-L
> 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: Mandar A. Ghosalkar
  INET: mghosalk_at_byer.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).