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 Report 能够查询视图.

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