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

[Prof Brian Ripley]

On Wed, 19 Apr 2006, Marc Schwartz (via MN) wrote:

> 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.

I don't think this helps: sqlFetch will create an (unnamed) R object 
containing the whole table and hence have the memory issues.  What you can 
do is use is a limit on the number of rows and use sqlFetchMore in a loop.

> 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).

And if you have an Oracle ODBC driver there are a whole host of ODBC 
clients to do this (on Unix you could just use isql in batch mode).

> Finally, there is an R e-mail list focused on databases:
>
>  https://stat.ethz.ch/mailman/listinfo/r-sig-db
>
> HTH,
>
> Marc Schwartz
>
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
>

-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595