CODEWITHSUNDEEP
rdatabaseoracle-databaseodbcrodbc
Mark White
Mark White

Reputation: 1496

RODBC::sqlSave() creating table, Warning: truncated to 255 bytes in column?

I am using RODBC to try and write a new table (a local data frame) to an Oracle database. The issue I am dealing with is that many of the fields are character variables, with lengths longer than 255 characters. I ran this code to initialize the table, per the RODBC vignette:

sqlSave(channel=conn, dat=SCHEMA.TABLE_NAME)

Where conn is an R object set up using odbcConnect and SCHEMA.TABLE_NAME is the name of the local data frame. R returned warning messages, and all of them said that

…truncated to 255 bytes in column…

The RODBC vignette mentions that many systems default to varchar(255) for characters. However, when I call sqlTypeInfo() on the conn object, it says that varchar2 has a COLUMN_SIZE of 4000.

How can I write this data frame to the database as a table, without truncating the character strings? I believe I could write a SQL query using sqlQuery to create this table manually, but I have about 175 columns, and do not want to write out information for every single column.

Upvotes: 2

Views: 1948

Answers (2)

Saleem Khan
Saleem Khan

Reputation: 749

sqlsave(channel=conn, dat=SCHEMA.TABLE_NAME, varTypes=c(column_name="varchar(500)"))

though i'm unsure what the easiest way of getting c(column_name="varchar(500)") full of all your relevant columns is

Upvotes: 0

AlvaroFV
AlvaroFV

Reputation: 21

I had the same problem when writing a data frame to a sql server db using the sqlSave function in RODBC.

It was solved through setting the column type to take more data before running the sqlSave command. I used varchar(500) and it did the trick.

If you were creating a table from R with a single column containing a long string, this is what worked for me:

    sqlQuery<-c('CREATE TABLE YourTable (ColumnName varchar(500));')
    channel <- odbcConnect("YourTable")
    sqlQuery(channel, paste(sqlQuery))
    close(channel)

After that writing to that table using sqlSave should work:

    sqlSave(odbcConnect("YourTable"),dataFrame,append=TRUE)

If you still get the truncated warning, try setting the varchar value to something greater than 500

Upvotes: 2

Related Questions