Skip to content

ROracle 1.1-5 and date fields?

3 messages · MacQueen, Don, Brian Ripley, Denis Mukhin

Original
MacQueen, Don
# 
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;
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,
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.
Loading required package: ROracle
Loading required package: DBI
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
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
Error in .oci.GetQuery(conn, statement, ...) :
Error in try({ : ORA-01805: possible error in date/time operation
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 Ripley
#
On 16/11/2012 19:11, MacQueen, Don wrote:
You need to ask the ROracle maintainer: he does not usually reply here.

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      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 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:

            

      
      
      
    

        
You need to ask the ROracle maintainer: he does not usually reply here.