sqlSave Updating Oracle DB Table -not working for Date field

Question

I have a Dataset as below

> head(resultsclassifiedfinal_MC_TC_P1)
  FEEDBACK_NUMBER Biz_Div_Num ACCURACY Category_Num CLASSIFIED_BY ACTIVE_IND CRT_BY_USR_NUM
1   20140211-1173         556   99.48%         2303           CMC          1         SYSTEM
2   20140211-1886         556    99.6%         2232           CMC          1         SYSTEM
3   20140209-0115         556   66.09%         2232           CMC          1         SYSTEM
4   20140202-0337         556    93.7%         2232           CMC          1         SYSTEM
5   20140203-0418         552      50%         2232           CMC          1         SYSTEM
6   20140303-1339         552   54.45%         2232           CMC          1         SYSTEM

And i am able to insert these records into an already existing table in Oracle DB

> library(RODBC)
> channel <- odbcConnect("R", uid="xxx", pwd="xxx@123") 
> sqlSave(channel,resultsclassifiedfinal_MC_TC_P1, tablename="table1", rownames=FALSE, append=TRUE,fast = FALSE,nastring = NULL)
> odbcClose(channel)

To table1 in Oracle Db - i add another column CRT_DTTM. My table structure is below

tmp <- sqlColumns(channel, "table1") 
> varspec <- tmp$TYPE_NAME
> varspec
[1] "VARCHAR2" "VARCHAR2" "VARCHAR2" "VARCHAR2" "VARCHAR2" "DECIMAL"  "VARCHAR2" "DATE"    

And in R Dataframe - i add a column (coreesponding to a new column in Oracle)

resultsclassifiedfinal_MC_TC_P1$CRT_DTTM <- Sys.Date()
FEEDBACK_NUMBER Biz_Div_Num ACCURACY Category_Num CLASSIFIED_BY ACTIVE_IND CRT_BY_USR_NUM   CRT_DTTM
1   20140211-1173         556   99.48%         2303           CMC          1         SYSTEM 2014-07-25
2   20140211-1886         556    99.6%         2232           CMC          1         SYSTEM 2014-07-25
3   20140209-0115         556   66.09%         2232           CMC          1         SYSTEM 2014-07-25
4   20140202-0337         556    93.7%         2232           CMC          1         SYSTEM 2014-07-25
5   20140203-0418         552      50%         2232           CMC          1         SYSTEM 2014-07-25
6   20140303-1339         552   54.45%         2232           CMC          1         SYSTEM 2014-07-25

When i try to insert into the table1,I get below error

> library(RODBC)
> channel <- odbcConnect("R", uid="wl_XXX", pwd="XXX@123") 
> sqlSave(channel,resultsclassifiedfinal_MC_TC_P1, tablename="table1", rownames=FALSE, append=TRUE,fast = FALSE,nastring = NULL)
Error in sqlSave(channel, resultsclassifiedfinal_MC_TC_P1, tablename = "table1",  : 
  unable to append to table ‘table1’
> odbcClose(channel)

The problem is with the newly added CRT_DTTM

> sapply(resultsclassifiedfinal_MC_TC_P1,class)
FEEDBACK_NUMBER    CLASS_DIV_CD        ACCURACY   CLASS_CATG_CD   CLASSIFIED_BY      ACTIVE_IND 
       "factor"        "matrix"        "factor"        "matrix"        "factor"       "numeric" 
 CRT_BY_USR_NUM        CRT_DTTM 
    "character"          "Date" 

> sapply(resultsclassifiedfinal_MC_TC_P1,mode)
FEEDBACK_NUMBER    CLASS_DIV_CD        ACCURACY   CLASS_CATG_CD   CLASSIFIED_BY      ACTIVE_IND 
      "numeric"       "numeric"       "numeric"       "numeric"       "numeric"       "numeric" 
 CRT_BY_USR_NUM        CRT_DTTM 
    "character"       "numeric" 

The Datatype in R and Oracle for that column is Date - but it is not working.I get error. Can anyone help , on this.

Update:

To make the Question Simpler,Pls. find below 'table1' in R

              FN   CRT_DTTM
1  20140526-0006 2014-07-30
2  20140528-0005 2014-07-30
3  20140613-0065 2014-07-30
4  20140528-0002 2014-07-30
5  20140522-0004 2014-07-30

 str(table1)
'data.frame':   5 obs. of  2 variables:
 $ FN      : Factor w/ 5 levels 
 $ CRT_DTTM: Date, format: "2014-07-30" "2014-07-30" "2014-07-30" ...

I cannot insert this column into Oracle DB.(I have table in Oracle with only 2 fields with varchar2 and Date as type). If i convert my CRT_DTTM column to character in R and then change the Date type to varchar2 in Oracle(development) - it is Inserting. However , i cannot change my Datatype in Oracle (in Production)

Answer 1

Oracle's default format for DATE is "DD-MON-YY". so i wrote the below code :

now <- format(Sys.time(), "%d-%b-%y")
resultsclassifiedfinal_MC_TC_P1$CRT_DTTM <- now
resultsclassifiedfinal_MC_TC_P1$UPD_DTTM <- now

After this , i am able to update the database with the date feild