[R] Inserting Date Field into Oracle table using ROracle

[Malladi, Sukhaswami]

Hello,

Attached is a mail regarding question how to insert Date field using ROracle

package. I am stuck with this problem and appreciate receiving help from 
gurus on this list.

Code used mainly is:

library(ROracle) ### --- Version 0.53
drv <- dbDriver("Oracle") 
con <- dbConnect( drv, "user/passwd") 
d <- data.frame(CDATE = "2004-03-10 10:12:00")
ps <- dbPrepareStatement(con, 
		"INSERT into DATEST (CDATE) VALUES ( :1 ) ", 
		bind=c( "character"))  ## -- c("date") does not work
sapply(d, class)
d$CDATE <- as.character(d$CDATE)
sapply(d, class)
dbExecStatement(ps,d) 

Error in oraExecStatement(ps, data, ...) : 
        RS-DBI driver: (ORA-01861: literal does not match format string )

Thanks for your help in advance,
Swami
(smalladi at lexgen.com)

----------------------------- Correspondence with David James
-----------------------

Dear David,

Thanks for your kind reply. I did what you suggested, coerced
d into a character vector. Now I get an Oracle error -

d <- data.frame(CDATE = "TO_DATE('2004-03-10 10:12:00','YYYY-MM-DD
HH:MI:SS')")
sapply(d, class)
d$CDATE <- as.character(d$CDATE)
sapply(d, class)
dbExecStatement(ps,d) 
Error in oraExecStatement(ps, data, ...) : 
        RS-DBI driver: (ORA-01858: a non-numeric character was found where a
numeric was expected
-----------------------------
ORA-01858 a non-numeric character was found where a numeric was expected

Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.

Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation
------------------

If I do 
d <- data.frame(CDATE = "2004-03-10 10:12:00")
instead of line 1 above, I get error :
Error in oraExecStatement(ps, data, ...) : 
        RS-DBI driver: (ORA-01861: literal does not match format string )
----------------
Cause: Literals in the input must be the same length as literals in the
format string (with the exception of leading white space). If the "FX"
modifier has been toggled on, the literal must match exactly, with no extra
white space.

Action: Correct the format string to match the literal.
------------

I do not know what I am doing wrongly. I will definitely post the experience
in R-help.

Kindly help,
Thanks
Swami



> -----Original Message-----
> From: David James [mailto:dj at research.bell-labs.com]
> Sent: Wednesday, March 10, 2004 8:15 AM
> To: Malladi, Sukhaswami
> Cc: David James
> Subject: Re: ROracle : insert dates
> 
> 
> Dear Swami,
> 
> One possible cause of your problem is that the dataframe "d"
> that you create may not have the date field "CDATE" as a string,
> but rather as a factor.  If this is the case, then you need to 
> coerce it to be a character vector, e.g.,
>   > d <- data.frame(CDATE = "2004-03-10")
>   d
>   CDATE
>   1 2004-03-10
>   > sapply(d, class)
>      CDATE
>   "factor"
>   > ## coerce CDATE to character
>   > d$CDATE <- as.character(d$CDATE)
>   > sapply(d, class)
>         CDATE
>   "character"
> 
> If this is indeed the problem, could you summary the result and
> post it to r-help so other people may be able to learn from your
> experience?
> 
> Regards,
> 
> --
> David
> 
> 
> Malladi, Sukhaswami wrote:
> > Hi
> > 
> > I am using ROracle for interacting between ORACLE and R. I 
> am able to insert
> > character and numeric data.
> > However, I am unable to insert date into a table despite 
> attempting many
> > methods. The code I used is as follows:
> > 
> > 	library(ROracle) ### --- Version 0.53
> > 	drv <- dbDriver("Oracle") 
> > 	con <- dbConnect( drv, "user/passwd") 
> > 
> > 	d <- data.frame( cbind( CDATE="TO_DATE('02-02-2004
> > 10:12:00','DD-MM-YYYY HH:MI:SS' )" ) )
> > 
> > 	lQry <- "INSERT into DATEST (CDATE) VALUES ( :1 ) "
> > 
> > 	ps <- dbPrepareStatement(con, "INSERT into DATEST 
> (CDATE) VALUES (
> > :1 ) ",
> > 			 bind=c( "character"))  ## --------- c("date")
> > gives error shown below
> > 
> > 	dbExecStatement(ps,d) 
> > 
> > Error in oraExecStatement(ps, data, ...) : 
> >         RS-DBI driver: (unrecognized S class factor )
> > 
> > > ps <- dbPrepareStatement(con, lQry, bind=c("date")) 
> > Error in oraPrepareStatement(conn, statement, bind, ...) : 
> >         RS-DBI driver: (unrecognized S class date )
> > > 
> > 
> > My question is : how do I insert date in the oracle table DATEST ?
> > 
> > SQL> desc DATEST;
> > 
> >  Name                Type
> >  ------------------ -------- 
> >  CDATE               DATE
> > 
> > 
> > platform i686-pc-linux-gnu
> > arch     i686             
> > os       linux-gnu        
> > system   i686, linux-gnu  
> > status                    
> > major    1                
> > minor    8.1              
> > year     2003             
> > month    11               
> > day      21               
> > language R                
> > 
> > I would be grateful for your kind help,
> > 
> > Thanks,
> > Swami
> > 
> > 
> > 
> **************************************************************
> ************* 
> >  The contents of this communication are intended only for 
> the addressee and
> > may contain confidential and/or privileged material. If you 
> are not the
> > intended recipient, please do not read, copy, use or disclose this
> > communication and notify the sender.  Opinions, conclusions 
> and other
> > information in this communication that do not relate to the official
> > business of my company shall be understood as neither given 
> nor endorsed by
> > it.  
> > 
> **************************************************************
> ************* 
> > 
> 


*************************************************************************** 
 The contents of this communication are intended only for th...{{dropped}}