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