Forum Stats

  • 3,770,079 Users
  • 2,253,061 Discussions
  • 7,875,301 Comments

Discussions

ROracle 1.3.2 dbWritetable

User_Q346L
User_Q346L Member Posts: 1 Green Ribbon

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

Answers

  • dbWriteTable uses all columns, but you can use execute itself with inserts and provide a subset of columns. For example, use dbSendQuery or dbGetQuery, and pass it the data frame with an insert statement.

    Here's a simplified example with dbGetQuery and an insert statement:

    # Create a table

    createStr <- "create table RORACLE_TEST(row_num number, col1 varchar2(40))"

    dbGetQuery(con, createStr)

    # Insert data

    insStr <- "insert into RORACLE_TEST values(:1, :2)"

    x <- c(1, 2, 3, 4, 5, 6, 7, 8, 9)

    y <- c("A","B","C","D","E","F","G","H","I")

    dbGetQuery(con, insStr, data.frame(x, y))

    # View data

    selStr <- "select * from RORACLE_TEST"

    dbGetQuery(con, selStr) 

    Sherry