Ken, Thanks for your detailed response. I thought of your remedies, but I perhaps didn't explain myself well enough. The Oracle we are using is Oracle 8, yes, ancient I know, but a fact of life. It has no binary_double type; that was introduced for Oracle 10 evidently. Second, I know there is a deviation, and I even know what it is in some cases, so a deviation check won't help me much. Following the extraction from the database, there is a sequence of complex numerical calculations. The end result is a fairly substantial discrepancy in the numerical result. Since we ported this entire piece of software, not just changing the database but other things as well, we'd like to know if it's all attributable to the database, or if there is some other source of the disagreement. It's not likely that a disagreement in the 14th significant figure from Oracle/sqlite could propagate to the 6th significant figure, but having dealt with numerics, you know this is possible.
Therefore my dilemma is this: are the observed differences in output due solely to the difference from the databases, or is there something else going on? The most straightforward way to ascertain this is to make sure the numbers coming out of the database are identical. I've done this in a limited way. With some painstaking digging into gdb and dbx, I found out how to read and set the integer (hex) representations of the doubles. Where I saw some important numbers differing slightly coming out of sqlite, I manually set them to what the Oracle result was, and let the computation proceed. At the end, the results were essentially the same as the unmodified sqlite application, so I know now that that discrepancy was insignificant. However, there are other numbers coming from other tables that haven't been checked in the same way. I was seeking one blanket way of temporarily having all the numbers extracted emulate the exact form of the Oracle numbers. It's not that this is the only way to proceed, but if such a method exists, it would make things much faster. Liam On Wed, Mar 26, 2008 at 1:02 PM, Ken <[EMAIL PROTECTED]> wrote: > Liam, > > I know a great deal about oracle internals, Oracle stores "numbers" as you > indicate in a bcd format. But It can be up to 22 bytes long. But usage in > oracle proc/proc++/sql/plsql is really dependent upon your native conversion > to host datatype. > > The number storage formats betweend Sqlite and Oracle are totally different. > I don't think there is much your going to be able to do since the storage is > different, most likely the precision is also going to be different and hence > the outputs. > > Maybe you could implement a deviation check in your sqlite code. If the > number produced fromsqlite matches oracle within a range +/- some number. > Then it is considered equal. > > Also as your article implies you could modify the oracle data type to be a > binary_float or binary_double which would store the value in a "native" ieee > format instead of the NUMBER (which is more accurate). > > Another thought: Role your own NUMBER data type !!! Create a function inside > sqlite that would basically implement an oracle Number representation. This > should then produce exact match to oracle (in theory). You could use a text > field or blob field to store the number datatype. Then apply your conversion > function to it! > > HTH, > Ken > > > > Liam Healy <[EMAIL PROTECTED]> wrote: I am porting a numerical application > from Oracle to SQLite. For the > > > most part, I have been successful, but there are slight disagreements > in the floating point number results. I have traced this back and > found a problem. According to > http://articles.techrepublic.com.com/5100-22_11-5224536.html, Oracle > by default stores floats as binary-coded decimal (BCD), and not > IEEE754 binary. SQLite on the other hand does > http://www.sqlite.org/datatype3.html: "REAL. The value is a floating > point value, stored as an 8-byte IEEE floating point number." For the > results of the application, it makes no difference how the numbers are > stored -- the differences in the 15th significant figure are > irrelevant. However, I would like to insure that there no > disagreements in the way the two applications operate (other than the > storage of floating point numbers), and for that I temporarily need > exact agreement on input numbers. I cannot change the Oracle > application, so I'm wondering if there's a wrapper or something I can > put around sqlite calls (or better, a mode that I can put sqlite in) > that will reproduce exactly the BCD format of Oracle. > > Thanks for any guidance. > > Liam > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
