[R] Inserting Date Field into Oracle table using ROracle
Malladi, Sukhaswami
smalladi at lexgen.com
Thu Mar 11 21:35:29 CET 2004
Thank you very much for the insight into the problem.
I am able to insert dates into oracle date field with
ROracle in the given date format with the restriction
of not being able to include time. The default time is
12:00:00 AM for all dates inserted.
However, when I use a timestamp field instead of date field,
I can insert time as well. The following code works :
....
require(ROracle)
con <- dbConnect("Oracle", "user/password")
dbGetQuery(con, "create table datest (d timestamp)")
ps <- dbPrepareStatement(con,"insert into DATEST values
(:1)",bind=c("character"))
d <- data.frame( d='14-MAR-00 11.16.22 AM' )
d$d <- as.character(d$d)
dbExecStatement( ps, d )
<OraPreparedStatement:(18793,0,1)>
dbCommit(con);
...
The date/time format is fixed ( DD-MON-YY HH.MI.SS AM )
(pl. note the 2 digit year )
Thanks once again for the help,
Regards,
Swami
> -----Original Message-----
> From: David James [mailto:dj at research.bell-labs.com]
> Sent: Thursday, March 11, 2004 11:43 AM
> To: Malladi, Sukhaswami
> Cc: 'r-help at stat.math.ethz.ch'
> Subject: Re: [R] Inserting Date Field into Oracle table using ROracle
>
>
> 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
***************************************************************************
The contents of this communication are intended only for th...{{dropped}}
More information about the R-help
mailing list