[R-sig-DB] RODBC with Oracle and 64-bit Linux (encore)

[Marc Schwartz]

OK.  What is interesting, albeit, it may be a red herring, is the -1 in the error message. Is that an indication of an integer wrap around or something more subtle?

Please be sure that you are running 64 bit R. Check the result of:

  .Machine$sizeof.pointer

It should return 8 if you are running 64 bit R. If it returns 4, you are running 32 bit R, which would conflict with the rest of the 64 bit tool chain.

If by chance you are running 32 bit R, you will need to install 64 bit R and reinstall your CRAN packages, including RODBC, since any C/FORTRAN code would be compiled as 32 bit programs.

Can you also perhaps try a query that should result in a small result set? Perhaps a single column from a single row?


Marc

On Nov 18, 2010, at 11:29 AM, Harlan Harris wrote:

> Quick response -- thanks! Nope, doesn't help.
> 
> >  library(RODBC)
> > hdl <- odbcConnect('ORACLE', rows_at_time=1)
> > sqlQuery(hdl, 'select * from catalog.program where rownum < 10', rows_at_time=1)
> Error in odbcQuery(channel, query, rows_at_time) : 
>   Calloc could not allocate (-1 of 22816) memory
> 
>  -Harlan
> 
> On Thu, Nov 18, 2010 at 12:26 PM, Marc Schwartz <marc_schwartz using me.com> wrote:
> On Nov 18, 2010, at 11:15 AM, Harlan Harris wrote:
> 
> > Hi all,
> >
> > I'm trying again to get the R<->Oracle toolchain to work on 64-bit Linux
> > (CentOS 5/RHEL 5). As previously established, it's nearly impossible,
> > although I'm awfully close now and just need one bit of help.
> >
> > Here's what works:
> >
> > I installed the 64-bit Oracle Instant Client Basic and ODBC rpms (version
> > 11.2.0.2.0).
> > export ORACLE_HOME=/usr/lib/oracle/11.2/client64/
> > export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
> > export TNS_ADMIN=/etc/oracle
> > set up /etc/oracle/tnsname.ora
> >
> > The version of unixODBC that's installed on Redhat 5 is 2.2.11. That version
> > is old and doesn't work with 64-bit Oracle Instant Client. Both Oracle and
> > Redhat are aware of this and stubbornly refuse to fix it. Therefore I
> > installed easysoft's ODBC Oracle driver. Note that this costs something like
> > $2000 to license, but if you've got Oracle, you can probably pay for it.
> >
> > The easysoft driver installs to /usr/local/easysoft. It includes unixODBC
> > 2.2.12. (I uninstalled the factor 2.2.11 version)
> >
> > I confirmed that it was properly configured and working by running
> > /usr/local/easysoft/unixODBC/bin/isql and querying my database. Username and
> > password are in /etc/odbc.ini.
> >
> > I symlinked /usr/local/easysoft/unixODBC/lib, /include, and /bin to
> > /usr/local/lib, /include and /bin, respectively.
> >
> > Now I install RODBC from source. It seems to work, finding sql.h, sqlext.h,
> > and -lodbc.
> >
> >> library(RODBC)
> >> hdl <- odbcConnect('ORACLE')
> >> sqlQuery(hdl, 'select * from catalog.program where rownum < 10')
> > Error in odbcQuery(channel, query, rows_at_time) :
> >  Calloc could not allocate (-1 of 22816) memory
> >
> >
> > So close! What am I missing?
> >
> > Thanks!
> >
> > -Harlan
> 
> 
> Harlan, glad to see some progress, albeit at a notable cost.
> 
> Try using 'rows_at_time = 1' in the calls to both odbcConnect() and sqlQuery().
> 
> I have found the need to use that setting, rather than the default 100, in order to successfully retrieve data from our Oracle server on OSX.
> 
> The smaller value may also help to workaround the memory allocation issue, presuming that something more subtle is not going on.
> 
> HTH,
> 
> Marc Schwartz
> 
> 


	[[alternative HTML version deleted]]