ORA-00036: 配置 toplink 使用绑定变量后超出了最大递归 SQL 级别数 (50)
我有一个使用 Toplink 进行持久性和 Oracle 数据库的应用程序。最近我遇到了性能问题，特别是在数据库/查询级别。我在一堆触发器和存储过程中有一大段逻辑，写得很糟糕，并提供了针对突变触发器问题的解决方法。该应用程序已经投入生产几年了，考虑到获得新版本批准和其他更高优先级的官僚机构，重写此代码是最后的解决方案。所以我正在寻找快速解决方案。
提高性能的一种解决方案是使用绑定变量。我的问题是，在toplink中的sessions.xml中添加bind-all-variables和cache-all-statements之后，在某个UI上，触发了蹩脚的代码，并且在进行此配置更改之前工作得很好，我收到此错误：
ORA-00036：超出了递归 SQL 级别的最大数量 (50)
我的问题是：为什么仅在添加绑定变量设置后才显示此错误？我能做些什么来让它与绑定变量一起工作，而不改变蹩脚的代码？
I have an application that uses Toplink for persistence and Oracle database. Recently I have had performance problems, especially on the db/query level. I have a big piece of logic in a bunch of triggers and stored procedures, badly written, with workarounds for the mutating trigger problem. The application is in production for a few years now, and rewriting this code the last solution, considering the bureaucracy of getting approval for a new release and other higher priorities. So I am looking for a quick fix.
One solution to improve the performance is to use bind variables. My problem is that after adding bind-all-variables and cache-all-statements in sessions.xml in toplink, on a certain UI, that triggers the crappy code, and worked just fine before making this config change, I get this error:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
My questions are: why is it showing this error only after I add the bind-variable setting? What can I do to make it work with bind variables, without changing the crappy code?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
“针对变异触发问题的解决方法”
这听起来很可怕，因为大多数解决方法都围绕自主事务展开，这些事务（a）工作得不是特别好，（b）可能算作递归 SQL。
另一方面，在 PL/SQL 中拥有逻辑通常意味着问题不会是绑定变量，因为您需要在 PL/SQL 中付出很大的努力才能不使用绑定。
在这种情况下，我想说你的时间最好花在用坚实的基础解决/替换任何变异表解决方法上，而不是绑定变量问题上。
"with workarounds for the mutating trigger problem"
That sounds scary as most of those workarounds revolve around autonomous transactions which (a) don't work particularly well and (b) probably count as recursive SQL.
On the other hand, having logic in PL/SQL generally means the problem won't be bind variables, as you need to make a big effort in PL/SQL to not use binds.
In this case, I'd say your time is better spent on addressing/replacing any mutating table workarounds with a solid base rather than bind variable issues.