2

I'm trying to write a table to an Oracle database using the ROracle package. This works fine, however all of the numeric values are showing the full floating point decimal representation on the database. For instance, 7581.24 shows up as 7581.2399999999998.

Is there a way of specifying the number of digits to be stored after the decimal point when writing the table?

I found a work around using Allan's solution here, but it would be better not to have to change the variable after writing it to the database.

Currently I write the table with code like this:

dbWriteTable(db_connection, "TABLE_NAME", table, overwrite = TRUE)

Thanks in advance.

Callum Savage
  • 341
  • 2
  • 7
  • I think this is an issue with floating-point numbers in general, ala https://stackoverflow.com/questions/9508518/why-are-these-numbers-not-equal. This issue affects most programming languages and computing environments, I don't think you can get away from it in SQL. – r2evans Oct 18 '20 at 02:44

1 Answers1

1

It's not elegant but maybe good programming to make the types and precisions explicit. I did it with something like:

if (dbExistsTable(con, "TABLE_NAME")) dbRemoveTable(con, "TABLE_NAME")
create_table <- "create table CAMS_CFDETT_2019_AA(
ID VARCHAR2(100),
VALUE NUMBER(6,2)
)"

dbGetQuery(con_maps, create_table)

ins_str <- "insert into TABLE_NAME values(:1, :2)"

dbGetQuery(con, ins_str, df)
dbCommit(con)

Essentially, it creates the table and specifies the types for each column and the precision. Then it fills in the values with those from the dataframe (df) in R. You just have to be careful that everything matches up in terms of the columns. If you assign a number to oracle with precision 2 (VALUE NUMBER(3,2) and then push a value from R with more decimals, it will round it to the assigned precision (2 in this example). It will not truncate it. So df$value = 3.1415 in R would become VALUE 3.14 in the Oracle table.

djhocking
  • 1,072
  • 3
  • 16
  • 28
  • 1
    Elegant or not, I've spent hours on this site (and others) trying to control the field types and precision when creating tables in Oracle. This is the first approach that worked perfectly. I had varying levels of success with others, but NUM fields were always NUM(38,0). Thank you. – gruvn May 19 '22 at 17:36