sqlSave更新用于日期字段的Oracle表-not
sqlSave更新用于日期字段的Oracle表-not
提问于 2014-07-25 14:28:33
我有数据集如下所示
> 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中的table1 --我添加了另一列CRT_DTTM。我的桌子结构在下面
tmp <- sqlColumns(channel, "table1")
> varspec <- tmp$TYPE_NAME
> varspec
[1] "VARCHAR2" "VARCHAR2" "VARCHAR2" "VARCHAR2" "VARCHAR2" "DECIMAL" "VARCHAR2" "DATE" 在R中，我添加了一个列(在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,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)问题在于新添加的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 -但它不工作，我得到错误。有人能帮忙吗，在这个问题上。
更新：
让问题变得简单点，Pls。在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中。(我在Oracle中有两个字段，其中只有varchar2和日期类型)。如果我将CRT_DTTM列转换为R中的字符，然后在Oracle(开发)中将日期类型更改为varchar2 -则是插入。但是，我不能在Oracle中更改数据类型(在生产中)
回答 1
Stack Overflow用户
回答已采纳
发布于 2014-08-26 11:49:11
Oracle的默认日期格式是“DD-MON”。所以我写了下面的代码：
now <- format(Sys.time(), "%d-%b-%y")
resultsclassifiedfinal_MC_TC_P1$CRT_DTTM <- now
resultsclassifiedfinal_MC_TC_P1$UPD_DTTM <- now之后，我可以用date feild更新数据库。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/24958084
复制相关文章
相似问题
腾讯云开发者
