[R] Creating a .txt file from an Oracle DB without creating an R object

[Marc Schwartz (via MN)]

On Wed, 2006-04-19 at 17:02 +0200, Paul wrote:
> Dear R-helpers,
>    
>     I am dealing with an Oracle database (using package RODBC). I use
> R in order to transform some Oracle tables into .txt files (using
> function sqlFetch from package RODBC and then function write.table).
> However, I cannot do it without creating an R object, which is rather
> restrictive for very big Oracle tables. Indeed, any R Object is stored
> into RAM, which can be of limited size.
>     Do you know if it is possible to directly create a .txt file,
> without creating an R object ?
>     Thank you in advance.
>    
>   P. Poncet
>    

Somebody else may have a better idea, but you could probably use either
sink() or capture.output() to send the data to a text file instead of
the console, thus not creating an R object. For example:

  capture.output(sqlFetch(channel, "YourTableName", colnames = TRUE),
                 "OutputFile.txt")

You will need to adjust options("width"), which defaults to 80 and would
cause the typical in-console line wrapping to occur. You would not want
this in your text file of course.  'width' can be set up to 10,000 by
default and could go higher, if you want to adjust the value in print.h
and re-compile R.

See ?options, ?sink and ?capture.output for more information.

Another reasonable question might be, if you are just taking data from
an Oracle table and pumping it into a text file, you could do this in
other ways outside of R, including using the Oracle SQL*Plus Instant
Client (ie. via the SPOOL command).

Finally, there is an R e-mail list focused on databases:

  https://stat.ethz.ch/mailman/listinfo/r-sig-db

HTH,

Marc Schwartz