| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: imported database inconsistencies
Steve,
another fellow victim of crappy cg_ref_codes table ...
I'd say you where clause should be ...
where rv_domain = <>
and whatever ...
this is a better way ... we resolved a lot of our problems when we went from
RBO => CBO.
Raj
-----Original Message-----
Sent: Thursday, May 22, 2003 4:08 PM
To: Multiple recipients of list ORACLE-L
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) .....
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).
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: Jamadagni, Rajendra
INET: Rajendra.Jamadagni_at_espn.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).
 |
 |