|
From: <mod...@co...> - 2012-08-30 18:20:20
|
Thanks Vincent, Perhaps the note on page 3-11 of the OCI Programmer's Guide, http://docs.oracle.com/cd/E14072_01/appdev.112/e10646.pdf is relevant here: "You may receive a round-off error when converting between FLOAT and NUMBER." Besides this loss of precision, they should have also documented the loss of range from (-1e308 to 1e308) down to (1e-87 to 1e127) when writing/reading 8 bit FLOATs to/from the database. In the JDBC world there's a useful flag called "SetFloatAndDoubleUseBinary", as shown in page 4-16 of http://docs.oracle.com/cd/E14072_01/java.112/e10589.pdf that when set "true" causes binary transfer, thereby avoiding conversion to NUMBER. There might be an easy fix if something similar already exists for OCI (I searched, but couldn't find). ----- Original Message ----- From: "vincent rogier" <vin...@ya...> To: mod...@co... Cc: orc...@li... Sent: Thursday, August 30, 2012 1:20:35 AM Subject: Re: how to write C double variable into Oracle BINARY_DOUBLE column without conversion (errors) to NUMBER? Hi, I'm on vacation until this weekend. I'll have a look at your problems asap once home and let you know. Regarding binding double, the binding call uses the oracle SQLT_FLOAT type with a size of 8 bytes. Oracle client may uses a number for converting value when it id binary double.... In this case, i will have to add a calls like OCI_BindBinaryDouble() to pass the SQLT_xxx value dedicated to binary boubles... Same for binary floats... Le 30 août 2012 03:24, < mod...@co... > a écrit : Hi Vincent, From experimentation of OCI_BindDouble in writing C double variables into an Oracle 11.2 table BINARY_DOUBLE column, such as, OCI_BindDouble(st, ":in_my_double", &my_double_variable); overflow errors occur when the value of my_double_variable is larger than the allowed value for Oracle NUMBER datatype. Thus, it appears the somewhere in the sequence of events the C variable in double-precision gets converted to Oracle NUMBER datatype, before being converted to BINARY_DOUBLE and stored in the table. This introduces conversion errors into the stored number in the database, and limits the useable range of values (BINARY_DOUBLE and C double-precision range from -1e308 to 1e308, versus NUMBER's -1e87 to 1e127). Per Oracle's documentation for Numerical Precedence (see below), this should not happen if Oracle receives double values. Does OCILIB use something similar to JDBC driver's setDouble()? If so, that would explain it, because Oracle's documentation http://docs.oracle.com/cd/E14072_01/java.112/e10589.pdf on page 4-16 states: "A call to the JDBC standard setDouble(int, double) method of the PreparedStatement interface converts the Java double argument to Oracle NUMBER style bits and send them to the database. In contrast, the setBinaryDouble(int, double) method of the oracle.jdbc.OraclePreparedStatement interface converts the data to the internal binary bits and sends them to the database." As as aside, fortunately, getDouble() avoids the conversion to NUMBER. Assuming you can reproduce this (just try a write a large number like 1e200 into a BINARY_DOUBLE column), do you know how I can get a C double variable accurately into an Oracle database BINARY_DOUBLE column using OCILIB? I suppose this would be of interest to anyone using BINARY_DOUBLE and BINARY_FLOAT with OCILIB. I do a lot of engineering/scientific work where precision and accuracy are critical. The only way I can think of is to convert the number to a string, and write that to the database, but that is not efficient. Ultimately I need to avoid conversion errors to NUMBER by preserving all the bits of BINARY_DOUBLE, along with its full range of values. Thanks in advance for any consideration. ----- Original Message ----- From: "vincent rogier" < vin...@ya... > To: mod...@co... Cc: orc...@li... Sent: Sunday, August 26, 2012 1:27:06 PM Subject: Re: installing latest OCILIB release; does OCILIB convert to Number when working with BINARY_DOUBLE? Hi, 1=> it is better to uninstall previous version and make a clean new install 2=> when using double C datatype in binding operation, the double host variable or array is directly passed to oracle. Bu you don't know how the oracle performs the values assigment. Regarding the documentation it seems to be a direct assignment. Regards Vincent Le 25 août 2012 16:18, < mod...@co... > a écrit : Hello, I have two OCILIB questions. 1) I installed OCILIB last year and have been happily using it. Now I see there is a new release with some features I'd like to use. If I wish to install the latest release, what is the procedure? That is, do I need to un-install anything? Or, do I simply follow the original installation instructions again, as documented in the OCILIB User Guide (and OCILIB with take care of itself)? 2) I don't see mention of BINARY_DOUBLE in the OCILIB User Guide, or anywhere else. Suppose I have a table in Oracle 11.2 database, with a column that is BINARY_DOUBLE datatype, and I use a PL/SQL stored procedure to select data from that column and place it, for example, in a variable out_data. When I retrieve out_data into the C variable myData using the following lines, double myData=0; ... OCI_Prepare(st, "begin get_my_data(:out_data); end;") OCI_BindDouble(st, ":out_data", &myData); OCI_Execute(st); will OCILIB convert the PL/SQL BINARY_DOUBLE variable out_data to Number datatype? Or, will OCILIB simply take the BINARY_DOUBLE variable out_data and map it exactly (via IEEE 754) into the C variable myData which is double datatype? I'm hoping there is a straight mapping to double so there's no conversion to Number that introduces conversion errors. If OCILIB does first convert to Number, is there a workaround? I also have this same question for OCI_BindArrayOfDoubles. I would think Oracle's Numerical Precedence would prevent conversion to Number, since it first looks at whether the variables involved are BINARY_DOUBLE and should therefore not convert to Number since out_data is BINARY_DOUBLE and myData is double. But I really have no idea what OCILIB is doing. ----------------------------------------------------------- The following is from Oracle documentation: Numeric Precedence Numeric precedence determines, for operations that support numeric datatypes, the datatype Oracle uses if the arguments to the operation have different datatypes. BINARY_DOUBLE has the highest numeric precedence, followed by BINARY_FLOAT, and finally by NUMBER. Therefore, in any operation on multiple numeric values: •If any of the operands is BINARY_DOUBLE, then Oracle attempts to convert all the operands implicitly to BINARY_DOUBLE before performing the operation. •If none of the operands is BINARY_DOUBLE but any of the operands is BINARY_FLOAT, then Oracle attempts to convert all the operands implicitly to BINARY_FLOAT before performing the operation. •Otherwise, Oracle attempts to convert all the operands to NUMBER before performing the operation. |
