来自 Oracle DB 的 SSRS 报告 - 使用存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1089692/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SSRS Report from Oracle DB - Use stored procedure
提问by ErikE
I am developing a report in Sql Server Reporting Services 2005, connecting to an Oracle 11g database. As you post replies perhaps it will help to know that I'm skilled in MSSQL Server and inexperienced in Oracle.
我正在 Sql Server Reporting Services 2005 中开发报告，连接到 Oracle 11g 数据库。当您发布回复时，了解我精通 MSSQL Server 和缺乏 Oracle 经验会有所帮助。
I have multiple nested subreports and need to use summary data in outer reports and the same data but in detail in the inner reports. In order to spare the DB server from multiple executions, I thought to populate some temp tables at the beginning and then query just them the multiple times in the report and the subreports.
我有多个嵌套的子报表，需要在外部报表中使用汇总数据，而在内部报表中使用相同的数据，但需要详细说明。为了避免数据库服务器多次执行，我想在开始时填充一些临时表，然后在报告和子报告中多次查询它们。
In SSRS, Datasets are evidently executed in the order they appear in the RDL file. And you can have a dataset that doesn't return a rowset. So I created a stored procedure to populate my four temp tables and made this the first Dataset in my report. This SP works when I run it from SQLDeveloper and I can query the data from the temp tables. However, this didn't appear to work out because SSRS was apparently not reusing the same session, so even though the global temporary tables were created with ON COMMIT PRESERVE ROWS my Datasets were empty.
在 SSRS 中，数据集显然是按照它们在 RDL 文件中出现的顺序执行的。并且您可以拥有一个不返回行集的数据集。所以我创建了一个存储过程来填充我的四个临时表，并将其作为我报告中的第一个数据集。当我从 SQLDeveloper 运行它时，这个 SP 工作，我可以从临时表中查询数据。然而，这似乎没有解决，因为 SSRS 显然没有重用同一个会话，所以即使全局临时表是使用 ON COMMIT PRESERVE ROWS 创建的，我的数据集也是空的。
I switched to using "real" tables and am now passing in an additional parameter, a GUID in string form, uniquely generated on each new execution, that is part of the primary key of each table, so I can get back just the rows for this execution.
我切换到使用“真实”表，现在传入一个附加参数，一个字符串形式的 GUID，在每次新执行时唯一生成，它是每个表的主键的一部分，所以我可以只取回行这次执行。
Running this from Sql Developer works fine, example:
从 Sql Developer 运行它可以正常工作，例如：
DECLARE
ActivityCode varchar2(15) := '1208-0916 ';
ExecutionID varchar2(32) := SYS_GUID();
BEGIN
CIPProjectBudget (ActivityCode, ExecutionID);
END;
Never mind that in this example I don't know the GUID, this simply proves it works because rows are inserted to my four tables.
没关系，在这个例子中我不知道 GUID，这只是证明它有效，因为行被插入到我的四个表中。
But in the SSRS report, I'm still getting no rows in my Datasets and SQL Developer confirms no rows are being inserted.
但是在 SSRS 报告中，我的数据集中仍然没有任何行，并且 SQL Developer 确认没有插入任何行。
So I'm thinking along the lines of:
所以我在思考：
- Oracle uses implicit transactions and my changes aren't getting committed?
- Even though I can prove that the non-rowset returning SP is executing (because if I leave out the parameter mapping it complains at report rendering time about not having enough parameters) perhaps it's not reallyexecuting. Somehow.
- Wrong execution order isn't the problem or rows would appear in the tables, and they aren't.
- Oracle 使用隐式事务而我的更改未提交？
- 即使我可以证明非行集返回 SP 正在执行（因为如果我省略参数映射，它会在报告呈现时抱怨没有足够的参数）也许它并没有真正执行。不知何故。
- 错误的执行顺序不是问题，或者表中会出现行，而事实并非如此。
I'm interested in any ideas about how to accomplish this (especially the part about not running the main queries multiple times). I'll redesign my whole report. I'll stop using a stored procedure. Suggest anything you like! I just need help getting this working and I am stuck.
我对如何实现这一点的任何想法感兴趣（尤其是关于不多次运行主查询的部分）。我会重新设计我的整个报告。我将停止使用存储过程。推荐任何你喜欢的东西！我只需要帮助让这个工作，我被卡住了。
If you want more details, in my SSRS report I have a List object (it's a container that repeats once for each row in a Dataset) that has some header values and then contains a subreport. Eventually, there will be four total reports: one main report, with three nested subreports. Each subreport will be in a List on the parent report.
如果您需要更多详细信息，在我的 SSRS 报告中，我有一个 List 对象（它是一个对数据集中的每一行重复一次的容器），它具有一些标题值，然后包含一个子报告。最终，将总共有四个报告：一个主报告，三个嵌套的子报告。每个子报告都将在父报告的列表中。
采纳答案by ErikE
Sigh.
叹。
The column being selected in the SP has a chardata type, but the SP had a varchar2input parameter for it. In Oracle, varchar2variables actually lose their trailing spaces (unlike in SQL Server). On top of that, I was doing RTrim()on the SSRS parameter as queried from the database.
在 SP 中选择的列有一个char数据类型，但 SP 有一个varchar2输入参数。在 Oracle 中，varchar2变量实际上会丢失其尾随空格（与 SQL Server 中不同）。最重要的是，我正在RTrim()处理从数据库中查询的 SSRS 参数。
For those who might want to know... SPs in Oracle can't return rowsets. So if you're running an SP from SSRS it's presumably to populate tables. But SSRS by default usually runs queries simultaneously. To make it run queries serially so that a later query can properly depend on the result of an earlier one, edit the dataset that runs the SP, click the "..." button next to the "Data source" dropdown, and check the "Use single transaction" checkbox at the bottom. As long as the SP is the first dataset listed in the RDL file, it should run first and the other datasets will wait until it has finished to run. Note: this setting applies to all datasets using the same data source.
对于那些可能想知道... Oracle 中的 SP 无法返回行集的人。因此，如果您从 SSRS 运行 SP，则可能是为了填充表。但 SSRS 默认情况下通常同时运行查询。要使其连续运行查询，以便以后的查询可以正确地依赖于较早的查询的结果，请编辑运行 SP 的数据集，单击“数据源”下拉列表旁边的“...”按钮，然后检查底部的“使用单笔交易”复选框。只要 SP 是 RDL 文件中列出的第一个数据集，它就应该首先运行，其他数据集将等到它完成运行。注意：此设置适用于使用相同数据源的所有数据集。
