Crystal Reports not returning data from some Oracle views using Oracle driver

2
votes

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.

1
oraclecrystal-reports
are you able to make connection using driver successfully? – Siva
Yes, the connection works fine and retrieves data most of the time, but does not retrieve data with a query like SELECT col1 FROM user2.viewname. The same query works in SQL Developer and SQL Plus. – AmyT
Connection made with which user? – Siva
The connection is from user1 in the example in the original question. User3 owns a table, grants SELECT WITH GRANT OPTION to user2. User2 creates aview, grants SELECT to user1. User1 selects from user2.viewname. – AmyT
I guess there can be the connection problem in CR.., If possible I would suggest to take one main report and in that 3 sub reports that points to 3 users and check the data correctly fetching or not – Siva

1 Answers

1
votes

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.