sqlSave更新Oracle DB表 – 不适用于Date字段

> 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

我能够将这些记录插入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)

对于Oracle Db中的table1 – 我添加了另一列CRT_DTTM.
我的表格结构如下

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

在R Dataframe中 – 我添加了一个列(对应于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

当我尝试插入table1时,我得到以下错误

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

问题在于新添加的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"

R和Oracle中该列的数据类型是Date – 但它不起作用.我得到错误.
任何人都可以提供帮助.

更新：

为了使问题更简单,请.在R中找到’table1’下方

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" ...

我无法将此列插入Oracle DB.(我在Oracle中只有2个字段,其中varchar2和Date作为类型).如果我将我的CRT_DTTM列转换为R中的字符,然后在Oracle(开发)中将Date类型更改为varchar2 – 它正在插入.但是,我无法在Oracle中更改我的数据类型(在生产中)

解决方法

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

在此之后,我可以使用日期更新数据库

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!