通过R连接Oracle数据库成功但无法获取有效数据的问题求助

问题背景 #

已通过32位R版本结合odbc/DBI包成功建立Oracle连接，连接代码如下：

library(odbc)
library(DBI)

con <- odbc::dbConnect(drv = odbc::odbc(), driver = "Oracle dans OraClient12Home1_32bit", 
                        DBQ = "<dbq>", Uid = "<uid>", 
                        PWD = "*****")

但遇到两个核心问题：

1. 执行DBI::dbListObjects(con)时返回大量如KEYSET_1344394的无效表，读取无有效数据；但RStudio连接面板中可见DGZ.DGZ_ROAD_TABLE这类层级化的有效表名。
2. 直接查询有效表DGZ.DGZ_ROAD_TABLE时触发错误：

sql = 'SELECT * FROM DGZ.DGZ_ROAD_TABLE'
DBI::dbGetQuery(con, sql)

错误信息：

Error: nanodbc/nanodbc.cpp:2809: HY000: [Oracle][ODBC][Ora]ORA-24359: OCIDefineObject n'a pas été appelé pour un type d'objet ou une référence
 
Warning message:
In dbClearResult(rs) : Result already cleared

解决方法 #

1. 过滤有效用户表，避开无效的KEYSET表 #

dbListObjects默认返回所有可见对象，包括系统临时表/键集表，可通过指定schema或查询Oracle数据字典来获取真实用户表：

• 指定schema查询表：

# 获取DGZ schema下的所有用户表
valid_tables <- DBI::dbListTables(con, schema = "DGZ")

• 直接查询Oracle数据字典视图（更精准）：

user_tables <- DBI::dbGetQuery(con, "SELECT table_name FROM all_tables WHERE owner = 'DGZ' AND table_name NOT LIKE 'KEYSET_%'")

2. 修复ORA-24359查询错误 #

该错误源于表中包含Oracle对象类型、REF类型或其他非基础数据类型，当前ODBC驱动对这类类型的支持不完善，可尝试以下方案：

方案一：仅查询基础类型字段

先查询表的字段类型，过滤掉非基础类型字段后再查询：

# 获取表的字段及类型信息
col_info <- DBI::dbGetQuery(con, "SELECT column_name, data_type FROM all_tab_columns WHERE table_name = 'DGZ_ROAD_TABLE' AND owner = 'DGZ'")
# 筛选支持的基础类型（可根据实际情况调整）
basic_types <- c("VARCHAR2", "NUMBER", "DATE", "CHAR", "TIMESTAMP")
valid_cols <- col_info$column_name[col_info$data_type %in% basic_types]
# 构造查询语句并执行
sql <- paste0("SELECT ", paste(valid_cols, collapse = ", "), " FROM DGZ.DGZ_ROAD_TABLE")
DBI::dbGetQuery(con, sql)

方案二：切换到ROracle包

ROracle是Oracle官方提供的R连接包，对Oracle特殊数据类型支持更优，需先配置Oracle客户端环境后使用：

install.packages("ROracle")
library(ROracle)
# 建立连接
con <- dbConnect(Oracle(), username = "<uid>", password = "*****", dbname = "<dbq>")
# 尝试查询全表
dbGetQuery(con, "SELECT * FROM DGZ.DGZ_ROAD_TABLE")

方案三：升级ODBC驱动

尝试将32位Oracle ODBC驱动升级到19c或21c版本，新版本对特殊数据类型的兼容性更好。

给IT部门的问询方向（无需了解R） #

针对不熟悉R的IT团队，可提出以下数据库/驱动层面的问题：

• 确认DGZ.DGZ_ROAD_TABLE表中是否包含自定义对象类型、REF类型或非基础数据类型，若有，能否提供这些类型的定义，或是否有方式将这类字段转换为基础数据类型供查询？
• 当前使用的OraClient12Home1_32bit驱动版本是否支持查询含对象类型的表？是否有可用的驱动升级包？
• 数据库用户<uid>对DGZ schema下的表是否拥有完整查询权限，包括访问表中对象类型底层数据的权限？
• KEYSET_xxx开头的表属于什么类型？是否为系统临时表/会话临时表？能否通过数据库配置或权限设置，让这类表不显示在元数据查询结果中？

内容的提问来源于stack exchange，提问作者Michaël