1

I've just a quick question to see how it comes that I get 2 different results for the same thing. We have two databases which are built up exactly the same in terms of structure. In both, there is a view which do a comparison between a varchar2(10) and a char(10) where the fields are only filled with a length of 7 (+3 spaces for the char off course).

Off course this is something wrong in our structure, but that's something different than my question.

How is it possible that one database is able to do the comparison (varchar2=char) and the other one not? Is there some Oracle-setting which can allow this.

Thanks for the help, Grts, Maarten

Maarten Kesselaers
  • 1,141
  • 2
  • 8
  • 19
  • 1
    What do you mean by "not able"? Do you not get the desired result? An error message? Monkeys flying out of the DB server? – Joachim Sauer Jun 05 '13 at 13:01
  • maybe use the trim function? Unclear what your issue is. – tbone Jun 05 '13 at 13:03
  • So, you're sure the data as well as the data types are identical in both databases? – René Nyffenegger Jun 05 '13 at 13:05
  • Both databases are identical. So the data for varchar2 is 7 positions long and the char is 10. With not able, I ment that a select query from table where varchar2-column = char-column gives a result on 1 database and on the other one not. Adding a rtrim on the char-column resolves the issue that we have with this comparison, but I would like to know how it comes that it works on one database and on the other one not. – Maarten Kesselaers Jun 05 '13 at 13:26
  • How one database populated from another? Is `Oracle Streams` capture/apply procedure used? – ThinkJet Jun 05 '13 at 14:23
  • Are you 100% sure the data for the char column is 10 positions long? Did you print the char column with delimiters to verify this? – Frank Schmitt Jun 05 '13 at 15:25
  • Exporting the char colomn to csv gives me '1342570 ';. I don't know how the export/import procedure is done exactly, but the column definitions and data is exactly identical. – Maarten Kesselaers Jun 07 '13 at 10:44

1 Answers1

1

It's probably bug 11726301 "Wrong Result with query_rewrite_enabled=false and Joins of CHAR with Other CHAR and VARCHAR2 Columns"

Fixed in 11.2.0.3

Workaround is to set query_rewrite_enabled=true

ThinkJet
  • 6,725
  • 24
  • 33