[R-sig-DB] ROracle 1.1-5 and date fields?

[Denis Mukhin]

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 using stats.ox.ac.uk] 
Sent: Friday, November 16, 2012 4:08 PM
To: MacQueen, Don
Cc: r-sig-db using 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 using 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 using r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-db