Persistence of data frames(R objects) into Database

Question

I have a database table lets say Table 1. Table 1 has 10 columns lets assume: column1,column2,column3,column4,column5,column6,column7,column8,column9,column10... I have a data-frame as

sample_frame<-data.frame(column1=1,column2=2,column3=3,column4=4)

I wish to persist the data frame i.e. sample_frame into my database table i.e. Table 1. presently I am using ROracle package to write into database. the code which I am using is as follows:

library(ROracle)
dbWriteTable(con, name="Table 1", value=sample_frame, row.names = FALSE, 
               overwrite = FALSE,append = TRUE,  schema ="sample_schema")

I have created connection object using dbConnect(), As far as integrity and null constraints of Table 1 is concerned, I have taken care of that. When I try to write into the table using dbWriteTable(), the following error is thrown:

"ORA-00947: not enough values"

Can someone correct the method I am using or provide me an alternative method of inserting selective columns(non-nullable columns) into the Table 1 while leaving other columns empty. I am using R 2.15.3

As it is saying.. You are creating sample with 4 columns and original table has 10 columns so you will surely get this error... — vrajs5
This error occurs when a SQL statement requires two sets of values equal in number — zx8754

vrajs5 vrajs5 · Accepted Answer · 2014-06-10T11:37:20

As I mentioned in my comment, you are creating sample_frame with lesser number of columns you are getting this error... Try this (if you actual table in database have same column names)

sample_frame<-data.frame(column1=1,column2=2,column3=3,column4=4,
                         column5=5,column6=6,column7=7,column8=8,
                         column9=9,column10=10)
library(ROracle)
dbWriteTable(con, name="Table 1", value=sample_frame, row.names = FALSE, 
               overwrite = FALSE,append = TRUE,  schema ="sample_schema")

Update

Considering your new requirement, I would suggest you prepare a query and use following

qry = #You update query
dbSendQuery(con, qry)

Persistence of data frames(R objects) into Database

1 Answers