Can't get full result set in R DBI

Question

That database is Oracle 12. I'm using R DBI with RODBC.

I have a complex query with multiple sub queries and WITH statements. The result set is only about 3000 rows, but it only returns 800 in R, and exactly 800 rows every time.

I've tried dbSendQuery followed by dbFetch, or just dbGetQuery by itself, setting n = 5000 and other values that are supposed to return all rows, but nothing works. I can successfully get less than 800 rows by setting n = 500, but not any more than 800.

As I add columns to the SELECT statement, the # of rows returned decreases. It isn't a JOIN issue, the full query works fine when running it natively, just not in R.

I've also tried getting results in chunks, using code as follows, but it will only obtain the first chunk before ending the loop and returning a value that says the full results have been reached:

while (!dbHasCompleted(rs)) {
    chunk <- dbFetch(rs, 100)
    print(nrow(chunk))
}

If I keep all WITH statements the same and retain all sub-queries, but reduce the final SELECT a single column from each WITH or sub-query, it's fine, I get all rows. And as I said, the full query works fine natively in SQL Developer.

It seems like there may be some size/memory limit, but for 3000 rows that shouldn't be an issue, and in any case I have tons of RAM, and the amount of resources being used by R when sending & fetching results in negligible, and the dataset is tiny. It's only the query that's complex.

Answer 1

I found the issue.

The problem was an invalid value in the results returned. The query worked fine in SQL Developer because that only returns results in chunks of 50. R DBI / RODBC fetches the full result set, though also in chunks (apparently) of 100, which is why it always fetched a round number of records: the 800+ block had the invalid value, so it only fetched the first 800 before getting an error.

Even trying to export query results from Developer didn't reveal the specific error, it simple failed. I had to page through all of the results to find the value in question and fix it. After that, everything worked fine.