I just joined and though I did quickly read the posting guide it is quite
possible not going to be a perfect posting. For one I tried to figure out how to
reply to an existing message on the topic. I gave up but figured I keep at least
the same subject.
In a gist I get an empty data frame after issuing the following select query
from R 2.9.1 to Oracle 10.2.0.1 64bit via RODBC 1.3 and Oracle 11g 32bit driver
-- all on the same Windows 2003 Server 64bit machine.
sqlTxnFvs = "select txn.*,pan.*,mcc.*,tmzip.*
from mddsg100bp txn
inner join mdd_panfvs pan on (txn.seq_no = pan.seq_no)
inner join mdd_mccfvs mcc on (txn.seq_no = mcc.seq_no)
inner join mdd_tmzipfvs tmzip on (txn.seq_no = tmzip.seq_no)
where is_test = '%d' and is_pos = '%d' and
ora_hash(txn.seq_no,%d,%d)=0"
dbConn =
odbcConnect("mysid",uid="myuser",pwd="mypass",case="toupper",believeNRows=FALSE)
tfg = sqlQuery(dbConn, sprintf(sqlTxnFvs,0,0,1/sratg-1,seeds[i]))
dbErr = odbcGetErrMsg(dbConn)
All of the tables except mddsg100bp are IOT tables (index organized tables aka
clustered index in MSSQL).
I set the believeNRows=FALSE.
I tried increasing the ODBC DSN config buffer from the default 64k to first 5M
and then 10M.
I started with columns from the narrower table mcc.* (38) and kept adding the
rest of the tables (tmzip.* 43, pan.* 62, and txn.* 64) one at a time - tfg is
correctly populated in all but the last case.
Selecting just txn.* fields works as well.
Furthermore it cannot be the buffer/data size as "select txn.field1,
pan.field2, mcc.field3, tmzip.field4" returns an empty data frame as well.
It cannot be the Oracle ODBC driver - tested query via ODBC Query util and it
works.
Also same query works over RJDBC (and of course all of the Oracle client tools).
I tried to trace at the ODBC and SQLNET levels but can't seem to make sense
of the latter or collect anything out of the former.
Thanks.
Edi Bice
[[alternative HTML version deleted]]
