|
From: vincent r. <vin...@ya...> - 2012-08-30 08:20:42
|
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. > |
