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?