ROracle: dbGetQuery works but dbListTables and other functions do not

Question

I installed ROracle (following the directions in the package) and successfully connected to our Oracle database.

I can run queries, using dbGetQuery, and get results just fine, e.g.:

> dbGetQuery(con, "select count(*) from table_name")
  COUNT(*)
1     6111

However, some of the other DBI/ROracle helper functions give no results:

> dbListTables(con)
character(0)

> dbReadTable(con, "table_name")
Error in .oci.GetQuery(con, qry) : 
  ORA-00942: table or view does not exist

Any ideas what may be the cause?

score 1 · Answer 1 · answered Sep 07 '16 at 16:19

1

In both cases, they work for me if I specify a schema argument, i.e.

dbListTables(con, schema = "my_schema")
dbReadTable(con,"table_name",schema = "my_schema")

Additionally, it appears from reading ?dbListTables that it has all and full arguments that control whether to look in all schemas, and whether to return the full schema name or just the table name.

answered Sep 07 '16 at 16:19

joran

169,992
32
429
468

Actually, I figured out that the schema is not necessary, but the table name should be in upper case in `dbReadTable` (but not in `dbGetQuery`). See my answer below. – Victor Kostyuk Sep 07 '16 at 21:43

score 0 · Answer 2 · edited May 23 '17 at 10:33

After spending too much time trying to get to the bottom of this issue, I want to record the answer for posterity. Actually having the schema is not necessary in dbReadTable, but putting the table name in uppercase is. That is, the call should be

dbReadQuery(con, "TABLE_NAME")

Why?

To find out how dbReadTable is different from a dbGetQuery with a select * from table call, I dug for the source:

> showMethods("dbReadTable")
Function: dbReadTable (package DBI)
conn="OraConnection", name="character"

So it's an S4 method. We use getMethod with the above signature:

> getMethod("dbReadTable", signature = c(conn = "OraConnection", name = "character"))
Method Definition:

function (conn, name, ...) 
{
    .local <- function (conn, name, schema = NULL, row.names = NULL, 
        ...) 
    .oci.ReadTable(conn, name, schema = schema, row.names = row.names)
    .local(conn, name, ...)
}
<environment: namespace:ROracle>

.oci.ReadTable can be found here, in the source of ROracle. All it does is check argument validity, call dbGetQuery, and set row names. The relevant part (calling dbGetQuery) is here:

# form name
if (is.null(schema))
  tab <- sprintf('"%s"', name)
else
  tab <- sprintf('"%s"."%s"', schema, name)

# read table
qry <- paste('select *',
               'from', tab)
res <- .oci.GetQuery(con, qry)

Note that if a schema isn't specified, the table name is used without the "schema." prefix. However, sprintf produces a quoted string for the table name, and quoted table names are case sensitive! (dbGetQuery just passes an unquoted table name which can be lowercase.)

So that's why dbGetQuery(con, "select count(*) from table_name") works, as does dbReadQuery(con, "TABLE_NAME") but dbReadQuery(con, "table_name") doesn't.

ROracle: dbGetQuery works but dbListTables and other functions do not

2 Answers2