Unintended recursiveness

Today I learned another PL/SQL feature… the hard way.

I was busy writing  some new code to import and process meta data. When testing the procedures performing a simple import  my procedure call kept running indefinitely and the OS load of my VM would go over 5. Definitely some code looping out of control. Been there, done that, doesn’t happen that often anymore. The other option would be some really bad performing SQL. Problem is the code I was testing contained neither. Killed my process, reviewed my code,reviewed the logging, didn’t see anything wrong, blamed my VM and tried again and expecting different behaviour…

So far for my hope on self healing code. The code looped again. From that point on I’ve been repeating the same approach over an over again. I really couldn’t understand what was going wrong. I had commented out most of my actual code to basically bare function calls and still the code was running endlessly. Finally as a last resort I compiled all my packages for debug and started debugging using SQL developer debugger. Still I didn’t see it at first until I noticed that I was stepping through the same code block twice. Apparently I was already in the loop. So continuing I looked carefully where it looped again.

Finally I noticed my error. In my procedure I called a function. Even used named notation for clarity. Nothing appeared to be wrong at all. The only thing being wrong was the name of the intended function. Instead of calling the function actually called the procedure name itself. Since both the procedure and the function share three out of five parameters with the same name I completely missed it. So there was my loop. I’d written an endless recursive loop which, since I was using merge statements, kept importing the same data over and over again.

Come to think of it I can’t really imagine a real life purpose for which I would intentionally write a recursive procedure call. I can imagine recursive function calls for some mathematical purposes like calculating factors of a number of which an example is below. But recursive procedures elude my imagination.

So if you read this and know a good example of a recursive procedure, please enlighten me. Otherwise a big +1 for adding a PL/SQL compiler warning for using recursive calls. (just like function returning without value). Since I hardly ever write recursive code when I actually do write it good chances are it wasn’t intentional like this case.

The warning itself might be recursive though :)

  • Are you sure your intentionally wrote a recursive procedure call
    • Really sure?
      • Realy sure?

Posted in Oracle Tagged with: ,

Leave a Reply