CODEWITHSUNDEEP
rrodbc
PaulHurleyuk
PaulHurleyuk

Reputation: 8159

RODBC connection to Oracle database not working, crashes R

I have a number of scripts that pull data from an Oracle 10g database via ODBC on Windows XP that have worked well for quite a while. I recently setup a new Oracle account to pull data from another schema of the same database, however when I test using that account and the different schema I get strange results, and can (reliably) sometimes kill R completely !

I connect to the database using odbcConnect, and the have tried believeNRows and rows_at_time with no difference

>imcon<-odbcConnect(sid, uid=uid, pwd=pwd, believeNRows = FALSE, rows_at_time=1)

> odbcGetInfo(imcon)
       DBMS_Name         DBMS_Ver  Driver_ODBC_Ver Data_Source_Name      Driver_Name       Driver_Ver         ODBC_Ver      Server_Name 
        "Oracle"     "10.02.0030"          "03.51"       "SID"    "SQORA32.DLL"     "09.02.0000"     "03.52.0000"       "SID"

I then try some queries (as simple as I can make it. This one returns zero rows (there are two rows in this table), and most of the column names are substituted for A.n

> sqlQuery(imcon, "select * from SCHEMA.USERS;")
 [1] PASSWORD                  USER_DISABLED             USE_DOMAIN_AUTHENTICATION ID                        DESCRIPTION              
 [6] ADMIN_USER                A                         A.1                       A.2                       A.3                      
[11] A.4                       A.5                       A.6                       A.7                       A.8                      
[16] A.9                       A.10                      A.11                      A.12                      A.13                     
[21] A.14                      A.15                      A.16                      A.17                      A.18                     
[26] A.19                      A.20                      A.21                      A.22                      A.23                     
[31] A.24                      A.25                      A.26                      A.27                      A.28                     
[36] A.29                      A.30                      A.31                      A.32                      A.33                     
<0 rows> (or 0-length row.names)
Warning messages:
1: In deparse(., width.cutoff = max(20, width - 10)) :
  invalid 'cutoff' for deparse, using default
2: In deparse(., width.cutoff = max(20, width - 10)) :
  invalid 'cutoff' for deparse, using default

This works, but returns zero rows and the two date columns are truncated to L and L.1

> sqlQuery(imcon, "select ID, DESCRIPTION, NAME, FIRST_NAME, LAST_NAME, DATE_CREATED, DATE_LAST_MODIFIED from SCHEMA.USERS;")
[1] ID          DESCRIPTION NAME        FIRST_NAME  LAST_NAME   L           L.1        
<0 rows> (or 0-length row.names)

This works fine, and returns data

> sqlQuery(imcon, "select ID, DESCRIPTION, NAME, FIRST_NAME, LAST_NAME, ADMIN_USER from SCHEMA.USERS;")
  ID DESCRIPTION   NAME FIRST_NAME LAST_NAME ADMIN_USER
1  1        <NA> SYSTEM       <NA>        NA       TRUE
2  2  Admin user  ADMIN      Admin        NA       TRUE

This seems to kill R (honestly)

> sqlQuery(imcon, "select ID, DESCRIPTION, NAME, FIRST_NAME, LAST_NAME, DATE_CREATED from SCHEMA.USERS;")

R dies. (both in StatEt and just in the RGui)

so my questions;

a) Does anyone know what is going wrong ?

b) Does anyone know what more steps I can take to diagnose ?

Upvotes: 1

Views: 1146

Answers (1)

PaulHurleyuk
PaulHurleyuk

Reputation: 8159

After some playing around and trying to simplify my problem, I noted quite strange output from SQLPlus (very, very long underlines for the column titles). This lead me to look at the data in SQL Developer, which showed me the column classes (I'm sure SQLPlus could have done this as well). It looks like most of the text fields are varchar2(4000) or similar, and the date fields are datestamp with timezone. It seems the varchars stop the results from being returned, and the datestamps kill R completely.

I amended my queries to strip these columns back to something normal by using substr for the varchar2, and to_char for the datestamp, for instance;

select substr(t1.STATUS_DETAIL,0,24) as DETAIL,
to_char(t1.DATE_CREATED,'YYYY-MM-DD HH24:MI:SS') AS DCREATED 
from SCHEMA.TABLE;

which has stopped R from crashing, and returns results ! I'll email the package maintainer for RODBC, and leave this answer for anyone else who has this issue.

Upvotes: 1

Related Questions