I am looking to read in the data that is stored as a CLOB value in my Oracle database. The contents simply are HTML that renders emails that we send through our CRM application. I want to re-create images of the email to include in my reports on crm performance.
I can successfully read the data into SPSS
using the following query, which converts the CLOB to a string of length (32750).
GET DATA
/TYPE=ODBC
/CONNECT='DSN=<MYDSN>;UID=<USER>;PWD=mypassword;Host=myhost;Port=myport;SID='+
'SID'
/SQL='SELECT * FROM mytable'
/ASSUMEDSTRWIDTH=32750.
CACHE.
EXECUTE.
DATASET NAME clob_query WINDOW=FRONT.
What I am looking to do is perform a similar query but read the data into R. I attempted the following query and got the error below:
> SQL <-"SELECT to_char(CONTENT) as content from REL_EMAIL_TEMPLATE"
> ds <- sqlQuery(ch, SQL, as.is=T, stringsAsFactors=F)
> ds
[1] "HY000 22835 [Oracle][ODBC][Ora]ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 5923, maximum: 4000)\n"
[2] "[RODBC] ERROR: Could not SQLExecDirect 'SELECT to_char(CONTENT) as content from REL_EMAIL_TEMPLATE'"
I am not sure how I can make the "buffer" larger, but figure that because I accomplish this in SPSS that R must be able to do this as well.
Many thanks in advance.
I feel like I must have attempted this solution before posting, but I came back to this problem and was able to get it running.
The only difference this time around is that I only tried to pull 1 record into R using a WHERE
clause.
Here is my query:
SQL <- "SELECT TO_CHAR(CONTENT) FROM RELATEMGR.REL_EMAIL_TEMPLATE WHERE primary_key = 1"
body <- sqlQuery(ch, SQL, stringsAsFactors=F)
Weird. Not sure if a different version of R matters either, but at least this got me to where I needed to be.
Just for completeness sake:
> sessionInfo()
R version 2.15.0 (2012-03-30)
Platform: i386-pc-mingw32/i386 (32-bit)
locale:
[1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United States.1252
[3] LC_MONETARY=English_United States.1252 LC_NUMERIC=C
[5] LC_TIME=English_United States.1252
attached base packages:
[1] stats graphics grDevices utils datasets methods base
other attached packages:
[1] RODBC_1.3-6
loaded via a namespace (and not attached):
[1] tools_2.15.0