[R] Inserting Date Field into Oracle table using ROracle
David James
dj at research.bell-labs.com
Thu Mar 11 18:42:31 CET 2004
I could not reproduce your problem, and I suspect that if you
try your example from the sqlplus utility you'd get the same
problem. I'd suggest you try to insert into your table directly
from sqlplus, and once you get it working then try the same
syntax from R. Oracle automatically coerces strings to DATE
by using the default format for your locale, so you need to make
sure that the strings you pass to INSERT conform to such default
format; e.g., to find out you could try
$ sqlplus user/password
SQL> select sysdate from dual;
SYSDATE
---------
11-MAR-04
SQL>
The following R session mimics your script:
require(ROracle)
con <- dbConnect("Oracle", "user/password")
dbGetQuery(con, "create table tst (d date, i integer)")
## make sure the input data.frame has the correct types
d <- data.frame(d = "11-MAR-04", i = as.integer(100))
d$d <- as.character(d$d) ## should *not* be a factor
## prepared statements automatically begin a new transaction
ps <- dbPrepareStatement(con, "insert into tst values (:1, :2)",
bind = c("character", "integer"))
dbExecStatement(ps, d) ## do the actual insert
## close the prepared statement to force a commit (otherwise you
## won't see the changes to the table)
dbClearResult(ps)
[1] TRUE
> dbReadTable(con, "tst")
D I
0 11-MAR-04 100
Hope this helps,
--
David
Malladi, Sukhaswami wrote:
> 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}}
>
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://www.stat.math.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
More information about the R-help
mailing list