Dear R-users, I was able to make ROracle package to connect to the DB (Oracle91, 64bit, on Solaris). But after executing siple SQL query, "fetch" commaned gives me an empty dataframe. ### RORACLE INSTALATION PROCEDURE ### R CMD INSTALL --configure-args='--enable-extralibs' --enable-oracle32=no ~/tmp/ROracle_0.5-5.tar.gz #since we have 64bit Oracle9i instalation PROBLEM: after executing simple statement, from within R, "fetch" function gives me the empty dataframe: ### CONNECTION, and SQL QUERY EXECUTION > ora <- dbDriver("Oracle") > con <- dbConnect(ora, "rado/only2admin" > dbListTables(con) character(0) > rs <- dbSendQuery(con, "desc * from si_r where id=498") > d <- fetch(rs, n= -1) > dim(d) [1] 0 3 str(d) data.frame': 0 obs. of 3 variables: $ ID : int $ DAY: chr $ R : num #### seems like connections has been established > dbGetInfo(ora) $drvName [1] "Oracle (ProC/C++)" $connectionIds $connectionIds[[1]] <OraConnection:(14939,0)> $fetch_default_rec [1] 500 $managerId <OraDriver:(14939)> $length [1] 10 $num_con [1] 1 $counter [1] 1 $clientVersion [1] "0.5-4" Maybe it is something stupid (and I need just a hint), or may be it is something major. Thanks in advance, Rado Bonk -- Dr. Radoslav Bonk European Commission - DG Joint Research Centre (JRC) Institute for Environment and Sustainability (IES) LM Unit - Natural Hazards Weather Driven Natural Hazards Action Via E. Fermi, TP 261, 21020 Ispra (Va), Italy Tel.: 0039-0332-786013 Fax: 0039-0332-786653 Webpage: http://natural-hazards.jrc.it/floods/
Dear Rado, I think you didn't get a proper db connection at all. You forgot to specify the database to connect to. On Wed, 14 Jul 2004, Rado Bonk wrote:> ### CONNECTION, and SQL QUERY EXECUTION > > ora <- dbDriver("Oracle") > > con <- dbConnect(ora, "rado/only2admin"Which database???> > dbListTables(con) > character(0)dbListTables would display something, if you had connected. You should get this working before you proceed. Regards, Michael
This is what I would try next if I were in your situation; I don't know it will help. Try con <- dbConnect(ora, "rado/only2admin at dbname") instead of con <- dbConnect(ora, "rado/only2admin" The default dbname is Sys.getenv("ORACLE_SID"), have you checked that? For myself, when I make connections to Oracle 9i using ROracle, I use the form dbConnect,ora, user='oracleuid',dbname='adbname',password='whatever') -Don At 10:38 AM +0200 7/14/04, Rado Bonk wrote:>Dear R-users, > >I was able to make ROracle package to connect to the DB (Oracle91, >64bit, on Solaris). But after executing siple SQL query, "fetch" >commaned gives me an empty dataframe. > >### RORACLE INSTALATION PROCEDURE ### >R CMD INSTALL --configure-args='--enable-extralibs' >--enable-oracle32=no ~/tmp/ROracle_0.5-5.tar.gz #since we have >64bit Oracle9i instalation > >PROBLEM: after executing simple statement, from within R, "fetch" >function gives me the empty dataframe: > >### CONNECTION, and SQL QUERY EXECUTION > > ora <- dbDriver("Oracle") > > con <- dbConnect(ora, "rado/only2admin" > > dbListTables(con) >character(0) >> rs <- dbSendQuery(con, "desc * from si_r where id=498") >> d <- fetch(rs, n= -1) >> dim(d) >[1] 0 3 >str(d) >data.frame': 0 obs. of 3 variables: >$ ID : int >$ DAY: chr >$ R : num > >#### seems like connections has been established >> dbGetInfo(ora) >$drvName >[1] "Oracle (ProC/C++)" > >$connectionIds >$connectionIds[[1]] ><OraConnection:(14939,0)> > > >$fetch_default_rec >[1] 500 > >$managerId ><OraDriver:(14939)> > >$length >[1] 10 > >$num_con >[1] 1 > >$counter >[1] 1 > >$clientVersion >[1] "0.5-4" > >Maybe it is something stupid (and I need just a hint), or may be it >is something major. > >Thanks in advance, > >Rado Bonk > > >-- >Dr. Radoslav Bonk >European Commission - DG Joint Research Centre (JRC) >Institute for Environment and Sustainability (IES) >LM Unit - Natural Hazards >Weather Driven Natural Hazards Action >Via E. Fermi, TP 261, 21020 Ispra (Va), Italy >Tel.: 0039-0332-786013 >Fax: 0039-0332-786653 >Webpage: http://natural-hazards.jrc.it/floods/ > >______________________________________________ >R-help at stat.math.ethz.ch mailing list >https://www.stat.math.ethz.ch/mailman/listinfo/r-help >PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html-- -------------------------------------- Don MacQueen Environmental Protection Department Lawrence Livermore National Laboratory Livermore, CA, USA
Michael, Yes you are right, I forgot to specify the dbname when posting in R-help list. But not in my R-code, since it is specified in ORACLE_SID variable on Linux. Using the proper syntax I still get an empty data frame: > library(ROracle) > drv <- dbDriver("Oracle") > con <- dbConnect(drv, "rado/mypassword at dea") > dbListTables(con) character(0) > q.sql <- c("select * from si_r where id=498") > rs <- dbSendQuery(con, q.sql) > data <- fetch(rs, n= -1) > dim(data) [1] 0 3 > dbGetStatement(rs) [1] "select * from si_r where id=498" > dbGetInfo(con) $dbname [1] "dea" $user [1] "rado" $passwd [1] "mypassword" $conType [1] NA $serverVersion [1] NA $protocolVersion [1] NA $threadId [1] -1 $resultSetIds $resultSetIds[[1]] <OraResult:(15551,0,2)> > data [1] ID DAY R <0 rows> (or 0-length row.names)> >>### CONNECTION, and SQL QUERY EXECUTION >> > ora <- dbDriver("Oracle") >> > con <- dbConnect(ora, "rado/only2admin" > > > Which database???-- Radoslav Bonk European Commission - DG Joint Research Centre (JRC) Institute for Environment and Sustainability (IES) LM Unit - Natural Hazards Weather Driven Natural Hazards Action Via E. Fermi, TP 261, 21020 Ispra (Va), Italy Tel.: 0039-0332-786013 Fax: 0039-0332-786653 Webpage: http://natural-hazards.jrc.it/floods/