Hi,
I'm using dbWritetable to append my oracle tables. The oracle tables has over 700+ columns while my dataframe only has 400+ columns.
May I know how to append the new dataframe to the table using dbWritetable and the leave the other columns as NULL? dbSendQuery or insert is not an option as the column names are complicated and number of columns is too many.
Also when I use dbWritetable in other database like mysql, it seems the function auto map the column names with the tables in the database, so the dataframe no need to be the same order as the tables columns in database. But Roracle does not seem to work as the same way. It must be the same number of columns and same order of columns with the tabels in db.
Is there any way I can use ROracle and dbWritetable to automatically match the column names of the data frame with the column names in the db when insert data?
Thanks
My code and error:
library(ROracle)
library(DBI)
con=dbConnect(dbDriver("Oracle"), username=username, password=pw,dbname = connect.string)
##write table with subset of columns
dbWriteTable(con,"test_table",data,row.names=F,append=T)
Error in .oci.WriteTable(conn, name, value, row.names = row.names, overwrite = overwrite, :
Error in .oci.GetQuery(con, stmt, data = value) :
ORA-00947: not enough values
##write table that columns not in order (data type not match)
dbWriteTable(con,"test_table",data,row.names=F,append=T)
Error in .oci.WriteTable(conn, name, value, row.names = row.names, overwrite = overwrite, :
Error in .oci.GetQuery(con, stmt, data = value) :
ORA-01722: invalid number