R - Writing to Oracle from R and data types are too large

53 Views Asked by At

I am writing a table from R to the Oracle database, however, I keep getting datatypes that are too long, such as every character variable being VARCHAR2(2000).

#create iris dataframe
iris <- iris

#convert species to character for testing
iris$Species <- as.character(iris$Species)

#get variable types from data frame and recode to Oracle data types
variable_types <- iris %>% 
    summarise_all(typeof) %>%
    gather %>%
    mutate(new_type = case_when(value == "double" ~ "NUMBER",
                                value == "character" ~ "VARCHAR2(200)"))

#create named list for field.types
var_type_list <- as.character(variable_types$new_type)
names(var_type_list) <- variable_types$key

#verify types
print(var_type_list)

#write to db
rc <- dbWriteTable(con, "IRIS", iris, row.names=FALSE, field.types = var_type_list)

But when I check in Oracle, this is what I see for the lengths

Name         Null? Type           
------------ ----- -------------- 
Sepal.Length       NUMBER         
Sepal.Width        NUMBER         
Petal.Length       NUMBER         
Petal.Width        NUMBER         
Species            VARCHAR2(4000) 

Am I using field.types incorrectly? Is there a better way to limit the length of the character variables?

0

There are 0 best solutions below