ORA-00036: maximum number of recursive SQL levels (50) exceeded after setting toplink to use bind variables

Question

I have an application that uses Toplink to save and an Oracle database. I have had performance issues lately, especially at the db / query level. I have most of the logic in a bunch of triggers and poorly written stored procedures, with workarounds for the mutating trigger problem. The application has been running for several years and rewrites this code with the latest solution, considering the bureaucracy of getting approval for the new version and other priority tasks. Therefore, I am looking for a quick fix.

One solution to improve performance is to use bind variables. My problem is that after adding bind-all-variables and cache-all-statements to session.xml in toplink, in a specific user interface that runs crappy code, and worked fine before changing this configuration change, I get this error:

ORA-00036: Maximum Number of Recursive SQL Levels Exceeded (50)

My questions are: why does it show this error only after I add the bind-variable parameter? What can I do to make it work with bind variables without changing the crappy code?

Answer 1

"with workarounds for the mutating trigger problem"

This sounds scary as most of these workarounds revolve around autonomous transactions that (a) do not work particularly well and (b) are probably considered recursive SQL.

On the other hand, the presence of logic in PL / SQL usually means that the problem will not bind variables, since you need to make great efforts in PL / SQL so that you do not use bindings.

In this case, I would say that your time is better spent addressing / replacing any mutational tabular workarounds with a solid base rather than variable bindings.