RODBC查询未返回数据 [英] RODBC query not returning data
本文介绍了RODBC查询未返回数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我完全为为什么在针对Oracle数据仓库编写查询时无法返回有效数据感到困惑.
I am completely stumped as to why I am not able to return valid data when writing a query against an Oracle data warehouse.
我以前见过此处，以及此
I have previously seen the post here, as well as this solution, but neither fixed my problem.
一些背景:
- 我每天都连接到Oracle数据仓库，通常在将数据提取到R中没有任何问题
- 当我在架构中列出表时，Table_Type被列为"TABLE"
- 我可以使用SPSS来查询数据库中的表，而不会出现问题.我正在尽一切努力从工作流程中删除SPSS.
我的代码尝试使用上面找到的解决方案:
My code which attempts to use the solutions found above:
ch <- odbcConnect(DSN, USER, PWD)
tmp <- sqlQuery(ch, "SELECT * from MV_BRM_COMMUNICATION_DM", rows_at_time=1)
这是返回的内容(错误的列名从第9列开始):
This is what is returned (incorrect column names start at column 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包中.我知道有很多抱怨，但是基本上，我所需要的只是拉取数据，并偶尔将其上传回数据库.
I would really like to stay with the RODBC package. I know there are quite a few complaints, but basically, all I need is to pull data down with the occasional upload back to the database.
非常感谢.
更新:这是有关表的一些信息:
UPDATE: Here is some info about the table:
> 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
推荐答案
这是我的想法，可以尝试一下.
Here's my thinking, and something to try.
对我来说很有趣，它失败的字段是表中TIMESTAMP的第一个实例.基于TIMESTAMP可能与之相关的理论，首先让我们在MV_BRM_COMMUNICATION_DM上创建一个视图，该视图将所有TIMESTAMP字段都转换为DATE:
It's interesting to me that the field it fails on is the first instance of a TIMESTAMP in the table. Working on the theory that TIMESTAMP may have something to do with it, first let's create a view on MV_BRM_COMMUNICATION_DM that casts all the TIMESTAMP fields to 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更改为从视图中读取，然后看看事情是否有所不同.
Now, change your SELECT to read from the view, and see if things work differently.
不能保证，因为我无法针对您的环境进行测试，但这是从头开始的.
Not guaranteed as I can't test against your environment, but it's something to start from.
分享并享受.
这篇关于RODBC查询未返回数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
