我完全为为什么在针对Oracle数据仓库编写查询时无法返回有效数据而感到困惑。
一些背景：
我的代码尝试使用上面找到的解决方案：
ch <- odbcConnect(DSN, USER, PWD) tmp <- sqlQuery(ch, "SELECT * from MV_BRM_COMMUNICATION_DM", rows_at_time=1)
这是返回的内容（不正确的列名从第9列开始）：
> tmp [1] COMM_ITEM_PK COMM_ERROR_PK COMM_ADDRESS_PRLO_PK [4] COMM_ADDRESS_LOPR_PK COMM_ADDRESS_PR_PK COMM_OUTBOUNDMESSAGE_ID [7] COMM_PIDM COMM_CREATED_BY ? [10] ?.1 ?.2 ?.3 [13] ?.4 ?.5 ?.6 [16] ?.7 ?.8 ?.9 [19] ?.10 ?.11 ?.12 [22] ?.13 ?.14 ?.15 [25] ?.16 ?.17 ?.18 [28] ?.19 ?.20 ?.21 [31] ?.22 ?.23 ?.24 [34] ?.25 ?.26 ?.27 [37] ?.28 ?.29 ?.30 [40] ?.31 ?.32 ?.33 [43] ?.34 ?.35 <0 rows> (or 0-length row.names)
这是我的连接信息：
> odbcGetInfo(ch) DBMS_Name DBMS_Ver Driver_ODBC_Ver Data_Source_Name Driver_Name "Oracle" "11.01.0070" "03.51" "MYDSN" "SQORA32.DLL" Driver_Ver ODBC_Ver Server_Name "09.02.0000" "03.80.0000" "myserver "
最后，我的会话信息：
> sessionInfo() R version 2.15.0 (2012-03-30) Platform: i386-pc-mingw32/i386 (32-bit) locale: [1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United States.1252 [3] LC_MONETARY=English_United States.1252 LC_NUMERIC=C [5] LC_TIME=English_United States.1252 attached base packages: [1] stats graphics grDevices utils datasets methods base other attached packages: [1] RODBC_1.3-5 loaded via a namespace (and not attached): [1] tools_2.15.0
我真的很想留在RODBC包中。我知道有很多抱怨，但是基本上，我所需要的只是拉取数据，并偶尔将其上传回数据库。
提前谢谢了。
更新：这是有关表的一些信息：
> sqlColumns(ch, "MV_BRM_COMMUNICATION_DM")[3:7] TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE 1 MV_BRM_COMMUNICATION_DM COMM_ITEM_PK 3 DECIMAL 19 2 MV_BRM_COMMUNICATION_DM COMM_ERROR_PK 3 DECIMAL 19 3 MV_BRM_COMMUNICATION_DM COMM_ADDRESS_PRLO_PK 8 DOUBLE PRECISION 0 4 MV_BRM_COMMUNICATION_DM COMM_ADDRESS_LOPR_PK 8 DOUBLE PRECISION 0 5 MV_BRM_COMMUNICATION_DM COMM_ADDRESS_PR_PK 8 DOUBLE PRECISION 0 6 MV_BRM_COMMUNICATION_DM COMM_OUTBOUNDMESSAGE_ID 12 VARCHAR2 144 7 MV_BRM_COMMUNICATION_DM COMM_PIDM 3 DECIMAL 19 8 MV_BRM_COMMUNICATION_DM COMM_CREATED_BY 12 VARCHAR2 1020 9 MV_BRM_COMMUNICATION_DM COMM_CREATION_DATE -4 TIMESTAMP(3) 2147483647 10 MV_BRM_COMMUNICATION_DM COMM_GENERIC_TEMPLATE_NAME 12 VARCHAR2 1020 11 MV_BRM_COMMUNICATION_DM COMM_TEMPLATE_NAME 12 VARCHAR2 2044 12 MV_BRM_COMMUNICATION_DM COMM_TEMPLATE_VERSION 12 VARCHAR2 1020 13 MV_BRM_COMMUNICATION_DM DATE_COMM_SENT -4 TIMESTAMP(3) 2147483647 14 MV_BRM_COMMUNICATION_DM COMM_DATE_SENT -4 TIMESTAMP(3) 2147483647 15 MV_BRM_COMMUNICATION_DM COMM_COMMUNICATION_CHANNEL 12 VARCHAR2 1020 16 MV_BRM_COMMUNICATION_DM COMM_SUBJECT 12 VARCHAR2 1020 17 MV_BRM_COMMUNICATION_DM COMM_EMAIL_PK 3 DECIMAL 19 18 MV_BRM_COMMUNICATION_DM COMM_TO_ADDRESS 12 VARCHAR2 1020 19 MV_BRM_COMMUNICATION_DM COMM_ISP_DOMAIN 12 VARCHAR2 4000 20 MV_BRM_COMMUNICATION_DM COMM_CCLIST 12 VARCHAR2 1020 21 MV_BRM_COMMUNICATION_DM COMM_BCCLIST 12 VARCHAR2 1020 22 MV_BRM_COMMUNICATION_DM COMM_REPLYTO 12 VARCHAR2 1020 23 MV_BRM_COMMUNICATION_DM COMM_SENDER 12 VARCHAR2 1020 24 MV_BRM_COMMUNICATION_DM COMM_REFERENCE_DESC 12 VARCHAR2 1020 25 MV_BRM_COMMUNICATION_DM COMM_OPTOUT_TOKEN 12 VARCHAR2 1020 26 MV_BRM_COMMUNICATION_DM COMM_DELIVERED_FLAG 8 DOUBLE PRECISION 0 27 MV_BRM_COMMUNICATION_DM COMM_OPTOUT_FLAG 8 DOUBLE PRECISION 0 28 MV_BRM_COMMUNICATION_DM COMM_OPTOUT_DATE 93 DATE 19 29 MV_BRM_COMMUNICATION_DM COMM_OPTOUT_CHANNEL 12 VARCHAR2 200 30 MV_BRM_COMMUNICATION_DM COMM_OPTIN_FLAG 8 DOUBLE PRECISION 0 31 MV_BRM_COMMUNICATION_DM COMM_OPTIN_DATE 93 DATE 19 32 MV_BRM_COMMUNICATION_DM COMM_OPTIN_CHANNEL 12 VARCHAR2 200 33 MV_BRM_COMMUNICATION_DM COMM_OPTLOCK 8 DOUBLE PRECISION 0 34 MV_BRM_COMMUNICATION_DM COMM_ERROR_FLAG 8 DOUBLE PRECISION 0 35 MV_BRM_COMMUNICATION_DM COMM_BOUNCED_FLAG 8 DOUBLE PRECISION 0 36 MV_BRM_COMMUNICATION_DM COMM_ONE_OFF 8 DOUBLE PRECISION 0 37 MV_BRM_COMMUNICATION_DM COMM_OWNERID 12 VARCHAR2 1020 38 MV_BRM_COMMUNICATION_DM COMM_OWNERNAME 12 VARCHAR2 2044 39 MV_BRM_COMMUNICATION_DM COMM_OWNER_KEY 3 DECIMAL 19 40 MV_BRM_COMMUNICATION_DM COMM_ORG_ID 3 DECIMAL 19 41 MV_BRM_COMMUNICATION_DM COMM_ORG_NAME 12 VARCHAR2 1020 42 MV_BRM_COMMUNICATION_DM COMM_CONTENT_PURGED 12 VARCHAR2 16 43 MV_BRM_COMMUNICATION_DM COMM_ACTIVITY_DATE 93 DATE 19 44 MV_BRM_COMMUNICATION_DM ACTIVITY_DATE 93 DATE 19 45 MV_BRM_COMMUNICATION_DM COMM_ITEM_PK 3 DECIMAL 19 46 MV_BRM_COMMUNICATION_DM COMM_ERROR_PK 3 DECIMAL 19 47 MV_BRM_COMMUNICATION_DM COMM_ADDRESS_PRLO_PK 8 DOUBLE PRECISION 0 48 MV_BRM_COMMUNICATION_DM COMM_ADDRESS_LOPR_PK 8 DOUBLE PRECISION 0 49 MV_BRM_COMMUNICATION_DM COMM_ADDRESS_PR_PK 8 DOUBLE PRECISION 0 50 MV_BRM_COMMUNICATION_DM COMM_OUTBOUNDMESSAGE_ID 12 VARCHAR2 144 51 MV_BRM_COMMUNICATION_DM COMM_PIDM 3 DECIMAL 19 52 MV_BRM_COMMUNICATION_DM COMM_CREATED_BY 12 VARCHAR2 1020 53 MV_BRM_COMMUNICATION_DM COMM_CREATION_DATE -4 TIMESTAMP(3) 2147483647 54 MV_BRM_COMMUNICATION_DM COMM_GENERIC_TEMPLATE_NAME 12 VARCHAR2 1020 55 MV_BRM_COMMUNICATION_DM COMM_TEMPLATE_NAME 12 VARCHAR2 2044 56 MV_BRM_COMMUNICATION_DM COMM_TEMPLATE_VERSION 12 VARCHAR2 1020 57 MV_BRM_COMMUNICATION_DM DATE_COMM_SENT -4 TIMESTAMP(3) 2147483647 58 MV_BRM_COMMUNICATION_DM COMM_DATE_SENT -4 TIMESTAMP(3) 2147483647 59 MV_BRM_COMMUNICATION_DM COMM_COMMUNICATION_CHANNEL 12 VARCHAR2 1020 60 MV_BRM_COMMUNICATION_DM COMM_SUBJECT 12 VARCHAR2 1020 61 MV_BRM_COMMUNICATION_DM COMM_EMAIL_PK 3 DECIMAL 19 62 MV_BRM_COMMUNICATION_DM COMM_TO_ADDRESS 12 VARCHAR2 1020 63 MV_BRM_COMMUNICATION_DM COMM_ISP_DOMAIN 12 VARCHAR2 4000 64 MV_BRM_COMMUNICATION_DM COMM_CCLIST 12 VARCHAR2 1020 65 MV_BRM_COMMUNICATION_DM COMM_BCCLIST 12 VARCHAR2 1020 66 MV_BRM_COMMUNICATION_DM COMM_REPLYTO 12 VARCHAR2 1020 67 MV_BRM_COMMUNICATION_DM COMM_SENDER 12 VARCHAR2 1020 68 MV_BRM_COMMUNICATION_DM COMM_REFERENCE_DESC 12 VARCHAR2 1020 69 MV_BRM_COMMUNICATION_DM COMM_OPTOUT_TOKEN 12 VARCHAR2 1020 70 MV_BRM_COMMUNICATION_DM COMM_DELIVERED_FLAG 8 DOUBLE PRECISION 0 71 MV_BRM_COMMUNICATION_DM COMM_OPTOUT_FLAG 8 DOUBLE PRECISION 0 72 MV_BRM_COMMUNICATION_DM COMM_OPTOUT_DATE 93 DATE 19 73 MV_BRM_COMMUNICATION_DM COMM_OPTOUT_CHANNEL 12 VARCHAR2 200 74 MV_BRM_COMMUNICATION_DM COMM_OPTIN_FLAG 8 DOUBLE PRECISION 0 75 MV_BRM_COMMUNICATION_DM COMM_OPTIN_DATE 93 DATE 19 76 MV_BRM_COMMUNICATION_DM COMM_OPTIN_CHANNEL 12 VARCHAR2 200 77 MV_BRM_COMMUNICATION_DM COMM_OPTLOCK 8 DOUBLE PRECISION 0 78 MV_BRM_COMMUNICATION_DM COMM_ERROR_FLAG 8 DOUBLE PRECISION 0 79 MV_BRM_COMMUNICATION_DM COMM_BOUNCED_FLAG 8 DOUBLE PRECISION 0 80 MV_BRM_COMMUNICATION_DM COMM_ONE_OFF 8 DOUBLE PRECISION 0 81 MV_BRM_COMMUNICATION_DM COMM_OWNERID 12 VARCHAR2 1020 82 MV_BRM_COMMUNICATION_DM COMM_OWNERNAME 12 VARCHAR2 2044 83 MV_BRM_COMMUNICATION_DM COMM_OWNER_KEY 3 DECIMAL 19 84 MV_BRM_COMMUNICATION_DM COMM_ORG_ID 3 DECIMAL 19 85 MV_BRM_COMMUNICATION_DM COMM_ORG_NAME 12 VARCHAR2 1020 86 MV_BRM_COMMUNICATION_DM COMM_CONTENT_PURGED 12 VARCHAR2 16 87 MV_BRM_COMMUNICATION_DM COMM_ACTIVITY_DATE 93 DATE 19 88 MV_BRM_COMMUNICATION_DM ACTIVITY_DATE 93 DATE 19
这是我的想法，可以尝试一下。
对我来说很有趣，它失败的字段是表中TIMESTAMP的第一个实例。基于TIMESTAMP可能与之相关的理论，首先让我们在MV_BRM_COMMUNICATION_DM上创建一个视图，该视图将所有TIMESTAMP字段都转换为DATE：
CREATE VIEW MV_BRM_COMM_DM_VIEW AS SELECT COMM_ITEM_PK, COMM_ERROR_PK, COMM_ADDRESS_PRLO_PK, COMM_ADDRESS_LOPR_PK, COMM_ADDRESS_PR_PK, COMM_OUTBOUNDMESSAGE_ID, COMM_PIDM, COMM_CREATED_BY, TO_DATE(COMM_CREATION_DATE) AS COMM_CREATION_DATE, COMM_GENERIC_TEMPLATE_NAME, COMM_TEMPLATE_NAME, COMM_TEMPLATE_VERSION, TO_DATE(DATE_COMM_SENT) AS DATE_COMM_SENT, TO_DATE(COMM_DATE_SENT) AS COMM_DATE_SENT, COMM_COMMUNICATION_CHANNEL, COMM_SUBJECT, COMM_EMAIL_PK, COMM_TO_ADDRESS, COMM_ISP_DOMAIN, COMM_CCLIST, COMM_BCCLIST, COMM_REPLYTO, COMM_SENDER, COMM_REFERENCE_DESC, COMM_OPTOUT_TOKEN, COMM_DELIVERED_FLAG, COMM_OPTOUT_FLAG, COMM_OPTOUT_DATE, COMM_OPTOUT_CHANNEL, COMM_OPTIN_FLAG, COMM_OPTIN_DATE, COMM_OPTIN_CHANNEL, COMM_OPTLOCK, COMM_ERROR_FLAG, COMM_BOUNCED_FLAG, COMM_ONE_OFF, COMM_OWNERID, COMM_OWNERNAME, COMM_OWNER_KEY, COMM_ORG_ID, COMM_ORG_NAME, COMM_CONTENT_PURGED, COMM_ACTIVITY_DATE, ACTIVITY_DATE FROM MV_BRM_COMMUNICATION_DM
现在，将您的SELECT更改为从视图中读取内容，看看事情是否有所不同。
不保证，因为我无法在您的环境中进行测试，但这是从头开始的。
分享并享受。
