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

[Harlan Harris]

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