ROracle 1.1-5 and date fields?
Don,
1) dbListTables now takes two additional arguments that control the display of tables/views in other schemas (assuming you have select access on these). Here is the description from the man page:
dbListTables(conn, schema = NULL, all = FALSE, full = FALSE, ...)
all: a logical specifying whether to look at all schemas.
full: a logical specifying whether to generate schema names. When
argument 'all' is 'TRUE', the output is a vector containing
schema names followed by the table names. Using 'matrix(...,
ncol = 2)' on the output produces a matrix where each row
corresponds to a table and the columns represent the schema
names and table names respectively.
If you are interested in particular schema you can use the schema argument:
schema: a case sensitive character string specifying a schema name
(or a vector of character strings for 'dbListTables').
2) We are getting ready to roll out ROracle 1.1-6 very soon. It has some fixes to date/time columns. A simple example seems to work fine in my development version:
R> con<-dbConnect(Oracle(), "scott", "tiger")
R> res<-dbGetQuery(con, "select sysdate cd, systimestamp ct from dual")
R> lapply(res, class)
$CD
[1] "POSIXct" "POSIXt"
$CT
[1] "POSIXct" "POSIXt"
R> res
CD CT
1 2012-11-16 16:22:40 2012-11-16 16:22:40
I'll install 1.1-5 and double check that this is indeed a problem to be sure that nothing else is at play here. I did get a report of a similar problem from another user today via email. He was having problem with
Select SYSDATE + 1 * Interval '1' Day FROM dual;
Denis
-----Original Message-----
From: Prof Brian Ripley [mailto:ripley at stats.ox.ac.uk]
Sent: Friday, November 16, 2012 4:08 PM
To: MacQueen, Don
Cc: r-sig-db at r-project.org
Subject: Re: [R-sig-DB] ROracle 1.1-5 and date fields?
On 16/11/2012 19:11, MacQueen, Don wrote:
I updated R and packages today, and am seeing some changes in ROracle behavior that I want to ask about. I'll describe the problems first;
You need to ask the ROracle maintainer: he does not usually reply here.
details follow. 1) dbListTables now lists only my personal tables previously it listed global tables (don't see any way to show an example of this) 2) simple queries to tables with date fields fail. For example,
dbGetQuery(con,"select * from wdrstats")
Error in .oci.GetQuery(conn, statement, ...) :
Error in try({ : ORA-01805: possible error in date/time operation
The table in this example has, among others, two date fields.
That is, "describe wdrstats;" returns (among others)
EFFDATE DATE
ENTERED NOT NULL DATE
By the way, I'm very happy to see that ROracle is being actively
maintained. Thank you!
Thanks
-Don
-------------------------------
This was a substantial upgrade; previously I had
ROracle 0.5-12 with R 2.14.1
now I have
ROracle 1.1-5 and R 2.15.2
(so ROracle now uses OCI; whereas before it did not)
R itself is built from source on a linux (RHEL5) machine.
I ran update.packages() and didn't see any problems.
Reinstalling ROracle using install.packages() succeeds (I can provide
a log if desired).
Running R on the same machine as the Oracle installation.
require(ROracle)
Loading required package: ROracle Loading required package: DBI
dbm <- Oracle() dbm
Driver name: Oracle (OCI) Driver version: 1.1-5 Client version: 11.2.0.1.0 Connections processed: 0 Open connections: 0 Interruptible: FALSE ## 'adbname' equals the value of env var ORACLE_SID
con <- dbConnect(dbm,user='macq',dbname='adbname',password='my.pwd') con
User name: macq Connect string: adbname Server version: 11.2.0.3.0 Server type: Oracle RDBMS Results processed: 0 OCI prefetch: FALSE Bulk read: 25 Statement cache size: 0 Open results: 0
dbGetQuery(con,"select * from wdrstats")
Error in .oci.GetQuery(conn, statement, ...) :
Error in try({ : ORA-01805: possible error in date/time operation
sessionInfo()
R version 2.15.2 (2012-10-26) Platform: x86_64-unknown-linux-gnu (64-bit) locale: [1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C [3] LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8 [5] LC_MONETARY=en_US.UTF-8 LC_MESSAGES=en_US.UTF-8 [7] LC_PAPER=C LC_NAME=C [9] LC_ADDRESS=C LC_TELEPHONE=C [11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C attached base packages: [1] stats graphics grDevices utils datasets methods base other attached packages: [1] ROracle_1.1-5 DBI_0.2-5
Brian D. Ripley, ripley at stats.ox.ac.uk Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595 _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB at r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-db