Hi Tom,
While inserting a double variable into a database columns it gets round off.The Database column is number(12,2).
when i tried inserting 2146327346.47,it was inserted as 2146327346.47. Similarly 2152186221.53
was insert as 2152186222.00 and 2248083191.91 as 2248083192.00.Can you tell me the reason and workaround for the same.Though this doesn't happen always i.e some values are inserted with exact precision and scale and some get round off.I have a Pro*C application.Code as below
#include<stdio.h>
#include<sqlca.h>
#include<string.h>
#include<stdlib.h>
EXEC SQL BEGIN DECLARE SECTION;
char *cnstr = "scott/tiger";
EXEC SQL END DECLARE SECTION;
void fn_sqlerrmsg(void);
void fn_sqlerrmsg()
{
printf("Error:%ld:%s",sqlca.sqlcode,sqlca.sqlerrm.sqlerrmc);
}
int main()
{
EXEC SQL WHENEVER sqlwarning CONTINUE;
EXEC SQL WHENEVER SQLERROR do fn_sqlerrmsg;
double d_readdata;
char ch,file_name[25];
FILE *fp;
char *line =NULL;
size_t len = 0;
ssize_t read;
EXEC SQL CONNECT :cnstr;
EXEC SQL WHENEVER NOTFOUND got to not_found;
strcpy(file_name,"file1");
fp = fopen (file_name,"r")
{
perror("error while opening file");
exit(EXIT_FAILURE);
}
while((read = getline(&line,&len,fp)!=-1)
{
sscanf(line,"%lf",&d_readdata);
prinft("%lf",d_readdata);
EXEC SQL
INSERT INTO test_insert(testdata) VALUES(:d_readdata);
}
fclose(fp);
EXEC SQL COMMIT;
return 1;
not_found:
printf("Record not found");
}
file1 is as below:
2146327346.47
2152186221.53
2248083191.91
377303849.19
The NUMBER data type is internally stored as a decimal floating point number. However, your bind variable is of C data type 'double', which is a binary floating number. Rounding is always expected when converting between binary and decimal floating point numbers. For example, 0.33 can be represented precisely in decimal format (as it is equal to 3/10 + 3/100) but it cannot be represented precisely in binary format, because it is not a sum of binary fractions (1/2 + 1/4 + 1/(2^n) + ...)
For example,
select to_char(to_binary_double(0.33),'TM') from dual;
gives .33000000000000002
The simplest workaround is to pass the numbers as strings and let the Oracle do the conversion with implicit or explicit TO_NUMBER.