Crystal Reports不使用Oracle驱动程序从某些Oracle视图返回数据 [英] Crystal Reports not returning data from some Oracle views using Oracle driver

问题描述

我正在使用Crystal Reports 2013，并且安装了Oracle ODAC 32和64位版本。如果创建新报告并使用 Oracle服务器数据源，则可以从我有权访问的任何表中进行选择。但是，我发现我从某些视图（不是全部）中检索不到数据。

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.

查询在SQL Plus或SQL Developer中工作正常。这些查询使用OLE DB和ODBC等过时的驱动程序检索Crystal Reports中的数据。

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.

我找不到在起作用或不起作用的视图之间的共性。我测试过的所有视图都属于同一个架构。它们都涉及属于第三种模式的表-也就是说，我以USER1身份登录，从属于USER2的视图中查询，该视图从属于USER2和USER3的表中提取数据。为了在表上创建视图并使该视图可用于其他视图，Oracle需要具有SELECT WITH GRANT OPTION权限。同样，查询在其他SQL工具中也可以正常工作。

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.

更新：我尝试以视图所有者的身份登录，但无法查询它们。我尝试以视图所有者（user2）和Crystal Reports所有者（user1）的身份查询基础表。两个用户都可以查询基础表。

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.

我正在研究有效的视图与无效的视图之间的差异。当我发现所有无效的视图都使用了引用列位置的ORDER BY子句时（例如：ORDER BY 2、1），我感到很乐观。我尝试重写ORDER BY以使用列名。没用试图删除ORDER BY子句。没用回到绘图板上。

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.

推荐答案

我发现问题在于Oracle视图正在使用特定于Oracle的非标准视图SQL。除了上面提到的 ORDER BY 2、1 ，在 WHERE子句中还有一个隐式日期转换，例如

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'

当我添加 TO_DATE 函数时：

WHERE date_col = TO_DATE ('01-JAN-2016', 'dd-MON-yyyy')

水晶报表可以查询视图。

这篇关于Crystal Reports不使用Oracle驱动程序从某些Oracle视图返回数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！