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

[Denis Mukhin]

A new version of ROracle 1.1-7 was uploaded to CRAN earlier today. We added a fix that will allow you to read DATE and TIMSTAMP columns with different versions of timezone files between your client machine and Oracle server. However, to read TIMESTAMP WITH TIMEZONE and TIMESTAMP WITH LOCAL TIMEZONE you will still need to have the same timezone file versions. For more info on this, please, take a look at the man page for dbReadTable. 

I would like to thank Don for posting this issue and Norbert for providing detailed instructions on how to update the timezone file version.

Thanks,
Denis

-----Original Message-----
From: norbert hoeller [mailto:nthdba using gmail.com] 
Sent: Monday, December 03, 2012 7:03 AM
To: r-sig-db using r-project.org
Subject: Re: [R-sig-DB] ROracle 1.1-5 and date fields?

Hi Don,

i ran into the same error.

A post on stackoverflow was inspiring:

http://stackoverflow.com/questions/7678485/oracle-ora-01805-on-oracle-11g-database

So I  upgraded the timezone-version, and now i, too, can read these pretty dates when using "ROracle"...

In short, what i did, to the good old database [as SYS-user]:


select version from v$timezone_file;
/*
   VERSION
----------
        11
*/
--- so, i upgraded to (the latest) TZ-version 14:
--- You have to be in Migrate-mode to run  dbms_dst.begin_upgrade, so first

shutdown immediate
---- and then
startup migrate;

begin
 dbms_dst.begin_upgrade(new_version => 14); end; /
---- restart the database (back to normal)
--  and run dbms_dst.upgrade_database:
shutdown immediate
---- and then
startup;

declare
  num_err number;
begin
   dbms_dst.upgrade_database(num_err);
   dbms_output.put_line('upgraded running into '||num_err||' errors');
   dbms_dst.end_upgrade(num_err);
   dbms_output.put_line('running into '||num_err||' errors to end upgrade'); end; /

select version from v$timezone_file;

/*
   VERSION
----------
        14
*/

---- you also might have to re-install/re-link the "ROracle"-package (as i did that without testing before-hand, i just cannot tell for sure :)

hth;

best regards,
norbert hoeller
Universitat fur Bodenkultur (BOKU)
A-1190 Wien


========== you 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; 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


--
Don MacQueen

Lawrence Livermore National Laboratory
7000 East Ave., L-627
Livermore, CA 94550
925-423-1062

	[[alternative HTML version deleted]]

_______________________________________________
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