[R] RODBC and Oracle: error "table does not exist"

[RINNER Heinrich]

Dear Marc,
thanks very much for your answer!!
Adding quotes to the table names didn't change anything (I had tried that
before),
but creating synonyms in Oracle did the trick!

Everything is working fine now, so thanks again.

-Heinrich.

> -----Ursprüngliche Nachricht-----
> Von: Marc Mamin [mailto:M.Mamin at intershop.de] 
> Gesendet: Montag, 21. Juli 2003 09:11
> An: 'RINNER Heinrich'
> Betreff: RE: [R] RODBC and Oracle: error "table does not exist"
> 
> 
> 
> 
> hallo,
> I'm using following syntax: which is working fine (with 
> Oracle 8.1.7.2):
> 
> channel <- ....
> sh3 <- sqlQuery(channel, "select .....")
> odbcClose(channel)
> myd <- data.frame(sh3)
> rm(sh3)
> 
> 
> Some other things you may check or try:
> 
> - add quotes to the table names :
> 
>  sqlFetch(channel, "\"ABTGRNAMEN\"")
> 
> - if you are not connecting as the user TKF , you may try to 
> create synonyms
> in oracle:
> 
> sqlplus TKF/PWD at XXXX
> 
> SQL> create synonym ABTGRNAMEN for TKF.ABTGRNAMEN
> 
> 
> HTH,
> 
> Marc Mamin
> 
> 
> 
> 
> -----Original Message-----
> From: RINNER Heinrich [mailto:H.RINNER at tirol.gv.at]
> Sent: Monday, July 21, 2003 8:53 AM
> To: 'r-help at stat.math.ethz.ch'
> Subject: RE: [R] RODBC and Oracle: error "table does not exist"
> 
> 
> I am re-trying a question I asked 12 days ago, to which 
> unfortunately I got
> no answer so far. Maybe someone who has succesfully established ODBC
> connections between R and Oracle can give a hint what I am 
> doing wrong?
> 
> -----Ursprüngliche Nachricht-----
> Von: RINNER Heinrich [mailto:H.RINNER at tirol.gv.at] 
> Gesendet: Mittwoch, 09. Juli 2003 15:34
> An: 'r-help at stat.math.ethz.ch'
> Betreff: [R] RODBC and Oracle: error "table does not exist"
> 
> Dear r-helpers!
> 
> I have trouble reading data from an Oracle data base using
> RODBC Version 1.0-3,
> R Version 1.7.1,
> Windows XP,
> Oracle8 ODBC Driver Version 8.1.6.4.0:
> 
> > library(RODBC)
> > channel <- odbcConnect(dsn="PAV32", case="oracle", 
> believeNRows=FALSE)
> > # ok, this was succesful
> > x <- sqlTables(channel)
> > x[37, ]
>      TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
> 37        <NA>         TKF ABTGRNAMEN      TABLE    <NA>
> 
> > # ok, so the table I am looking for ("ABTGRNAMEN") is there, but:
> > sqlFetch(channel, "ABTGRNAMEN")
> [1] "[RODBC] ERROR: Could not SQLExecute"                     
>               
> [2] "S0002 942 [Oracle][ODBC][Ora]ORA-00942: table or view 
> does not exist\n"
> > # I also tried:
> > sqlFetch(channel, "TKF.ABTGRNAMEN")
> Error in odbcTableExists(channel, sqtable) : 
>         TKF.ABTGRNAMEN : table not found on channel
> 
> What am I doing wrong here?
> It doesn't work with other tables as well; on the other hand, 
> connecting to
> the table(s) in MS Access works fine using the same ODBC driver.
> 
> Best regards,
> Heinrich.
> 
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://www.stat.math.ethz.ch/mailman/listinfo/r-help
> 
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://www.stat.math.ethz.ch/mailman/listinfo/r-help
>