[R] Reading Oracle SQL Developer BLOB/CLOB files into R (Packages: DBI, foreign, RODBC, ROracle)

[hardworker]

Hi everyone,

I have been following these forums closely for the last few months but this
is my first time posting. Basically I am trying to get an Oracle SQL
Developer Binary Large Object(BLOB/CLOB) file in as an R object in R. For
those not familiar with a BLOB/CLOB file, it is basically lik a table that
has cells that contain tables or files. By converting the large table into a
BLOB/CLOB file the size of the file can be greatly reduced and processing
for various applications improved.

I am approaching this problem with the help of a colleague who is looking at
solutions from a Java point of view while I am looking at solutions from a R
point of view. We have searched and found many packages such as:
- RODBC (Doesn't support BLOB objects)
- ROracle (Received an error message)
- Foreign(Seems to enable reading of SAS, minitab, SPSS and many other
software packages into R but not BLOB columns)
-DBI (Enables direct connection to R but had errors)

Below I present the error message as well as what we think is going on with
R when it is trying to convert the BLOB object into an R object (We could be
wrong so please correct us if we are wrong):

 We believe ROracle/DBI is capable because it did not return error when
reading from BLOB column, but instead it converted the column into
String(255) (This is what we think it did based on following message)

----------------------------------------------------------------------------
> rs <- dbSendQuery(con, "SELECT CLOB_COL from R_BLOB_TEST where ROW_ID=1")
Warning message:
In oraExecStatement(ps, ora.buf.size = as.integer(ora.buf.size)) :
  RS-DBI driver warning: (unkown ORA type 112 (extracted as STRING(255)))

The Ora type 113 and 112 seem to be pointing to CLOB and BLOB objects as
being unrecognised. Is there anyway to fix this?

So far our attempts on the R side have not been good but we have found lots
of material on using java to convert a BLOB file into another object like an
XML then reading that into R. But there are two more packages: rJava and
RUniversal that may enable us to use Java jar files or the Java language to
convert to R itself. I personally don't understand these java packages too
well but my colleague will be looking at them today in more detail but if
anyone can shed some light on this problem it would be a great help.

Best Regards,

hardworker

--
View this message in context: http://r.789695.n4.nabble.com/Reading-Oracle-SQL-Developer-BLOB-CLOB-files-into-R-Packages-DBI-foreign-RODBC-ROracle-tp4197551p4197551.html
Sent from the R help mailing list archive at Nabble.com.