Is there a way of specifying the precision and scale of a numeric variable when writing a table using ROracle?

Question

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.

Answer 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.