RODBC - Select * 在 Oracle 上失败，但可以指定列

发布于 2025-01-03 14:13:58 字数 1446 浏览 0 评论 0原文

我很困惑。使用 RODBC 并连接到 Oracle 数据库，我已使用以下命令成功探索了我的表：

sqlColumns(channel, "tablename", schema="schema")$COLUMN_NAME

我什至可以使用以下命令查询表：

temp <- sqlQuery(channel, "SELECT Col1, Col2 FROM table")

并返回数据。

但是，这无法返回有效数据：

temp <- sqlQuery(channel, "SELECT * FROM table")

这就是返回的内容：

 [1] PRIMARY_KEY    LASTMODIFIEDBY ?              ?.1            ?.2           
 [6] ?.3            ?.4            ?.5            ?.6            ?.7           
[11] ?.8            ?.9            ?.10           ?.11           ?.12          
[16] ?.13           ?.14          
<0 rows> (or 0-length row.names)

关于为什么 SELECT * 失败但我在指定列时能够返回有效数据的任何想法？为了完整起见，我在下面列出了 R 和 Oracle 详细信息。

> R.Version()
$platform
[1] "i386-pc-mingw32"

$arch
[1] "i386"

$os
[1] "mingw32"

$system
[1] "i386, mingw32"

$status
[1] ""

$major
[1] "2"

$minor
[1] "14.1"

$year
[1] "2011"

$month
[1] "12"

$day
[1] "22"

$`svn rev`
[1] "57956"

$language
[1] "R"

$version.string
[1] "R version 2.14.1 (2011-12-22)"

> odbcGetInfo(channel)
       DBMS_Name         DBMS_Ver  Driver_ODBC_Ver Data_Source_Name      Driver_Name 
        "Oracle"     "11.01.0070"          "03.51"       "xxxxxxxx"    "SQORA32.DLL" 
      Driver_Ver         ODBC_Ver      Server_Name 
    "09.02.0000"     "03.80.0000"       "xxxxxxxxx"

原文

I am stumped. Using RODBC and connecting to an Oracle database, I have successfully explored my table using the following:

sqlColumns(channel, "tablename", schema="schema")$COLUMN_NAME

I can even query the table using:

temp <- sqlQuery(channel, "SELECT Col1, Col2 FROM table")

and data are returned.

However, this fails to return valid data:

temp <- sqlQuery(channel, "SELECT * FROM table")

This is what returned:

 [1] PRIMARY_KEY    LASTMODIFIEDBY ?              ?.1            ?.2           
 [6] ?.3            ?.4            ?.5            ?.6            ?.7           
[11] ?.8            ?.9            ?.10           ?.11           ?.12          
[16] ?.13           ?.14          
<0 rows> (or 0-length row.names)

Any ideas as to why the SELECT * fails but I am able to return valid data when specifying the columns? For completeness, I listed the R and Oracle details below.

> R.Version()
$platform
[1] "i386-pc-mingw32"

$arch
[1] "i386"

$os
[1] "mingw32"

$system
[1] "i386, mingw32"

$status
[1] ""

$major
[1] "2"

$minor
[1] "14.1"

$year
[1] "2011"

$month
[1] "12"

$day
[1] "22"

I am stumped.  Using RODBC and connecting to an Oracle database, I have successfully explored my table using the following:
sqlColumns(channel, "tablename", schema="schema")$COLUMN_NAME

I can even query the table using:
temp <- sqlQuery(channel, "SELECT Col1, Col2 FROM table")

and data are returned.
However, this fails to return valid data:
temp <- sqlQuery(channel, "SELECT * FROM table")

This is what returned:
 [1] PRIMARY_KEY    LASTMODIFIEDBY ?              ?.1            ?.2           
 [6] ?.3            ?.4            ?.5            ?.6            ?.7           
[11] ?.8            ?.9            ?.10           ?.11           ?.12          
[16] ?.13           ?.14          
<0 rows> (or 0-length row.names)

Any ideas as to why the SELECT * fails but I am able to return valid data when specifying the columns?  For completeness, I listed the R and Oracle details below.
svn rev`
[1] "57956"

$language
[1] "R"

$version.string
[1] "R version 2.14.1 (2011-12-22)"

> odbcGetInfo(channel)
       DBMS_Name         DBMS_Ver  Driver_ODBC_Ver Data_Source_Name      Driver_Name 
        "Oracle"     "11.01.0070"          "03.51"       "xxxxxxxx"    "SQORA32.DLL" 
      Driver_Ver         ODBC_Ver      Server_Name 
    "09.02.0000"     "03.80.0000"       "xxxxxxxxx"