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

[norbert hoeller]

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