I'm using Crystal Reports 2013 and have Oracle ODAC 32 and 64 bit versions installed. If I create a new report and use the "Oracle Server" data source, I can select from any tables to which I have access. However, I find I retrieve no data from some, not all, views.
The queries work fine in SQL Plus or SQL Developer. The queries retrieve data in Crystal Reports using outdated drivers like OLE DB and ODBC.
I can't find a commonality between the views that do or don't work. All the views I've tested with belong to the same schema. they all involve tables that belong to a third schema -- that is, I log in as USER1, query from a view belonging to USER2, which pulls data from tables belonging to USER2 and USER3. In order to create a view on a table and make that view available to others, Oracle requires SELECT WITH GRANT OPTION permission, which is in place. Again, the queries work fine in other SQL tools.
UPDATE: I've tried logging in as the owner of the views and was unable to query them. I've tried querying the underlying tables as the view owner (user2) and as the Crystal Reports owner (user1). Both users are able to query the underlying tables. The view itself seems to be the problem.
I'm studying the differences between the views that work and the views that don't work. I was optimistic when I found that the views that don't work were all using ORDER BY clauses that referenced column position (ex: ORDER BY 2, 1). I tried rewriting the ORDER BY to use column names. Didn't work. Tried removing the ORDER BY clauses. Didn't work. Back to the drawing board.
I found that the problem was that the Oracle view was using Oracle-specific, non-standard SQL. In addition to the ORDER BY 2, 1 mentioned above, there was an implicit date conversion in the WHERE clause e.g.
WHERE date_col = '01-JAN-2016'
When I added the TO_DATE function:
WHERE date_col = TO_DATE ('01-JAN-2016', 'dd-MON-yyyy')
Crystal Report was able to query the view.