Malladi, Sukhaswami
2004-Mar-10 22:29 UTC
[R] Inserting Date Field into Oracle table using ROracle
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}}
David James
2004-Mar-11 17:42 UTC
[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
Malladi, Sukhaswami
2004-Mar-11 20:35 UTC
[R] Inserting Date Field into Oracle table using ROracle
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}}