[R] Creating a .txt file from an Oracle DB without creating an R object
Prof Brian Ripley
ripley at stats.ox.ac.uk
Wed Apr 19 18:14:46 CEST 2006
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),
> 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:
> Marc Schwartz
> R-help at stat.math.ethz.ch mailing list
> 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
More information about the R-help