Monthly Archives: January 2014

CFStoredProc – When Oracle and CF stop talking…

Today I had a coworker run into something that has caused me hours of pulling my hair out. He had a stored procedure in Oracle that he made some minor changes to, and them BAM! CF errors. There were several different errors served up from CF including (paraphrased) “wrong number/types of arguments” or “no statement parsed” or my favorite “unsupported datatype”. I am going from memory since I am at home, but you get the flavor of the messages even if my language is off.

I run into this problem about once every 8-10 months. Fortunately, I have hit it enough that I vaguely remember that it isn’t me. It is Oracle/CF not playing nice together.

Here is the scenario:
– Write a procedure that compiles fine. Runs from the command prompt and from CF pages just fine.
– Make some change to the procedure like adding an input variable.
– Update cfstoredproc arguments to match new Oracle definition
– Fail. Hard… unsympathetic fail. (See above error messages)
– Sometimes I find swearing under my breath helps at this point, but while I feel better, it does nothing to clear the error.

What I have gleaned from experience and some online resources is that CF is caching the call to the stored proc. The stored proc changed, but CF isn’t ready to try new things.

So the next question is “Great… now how do I fix it?”

(I should also mention that the call to the stored proc is in a CFC)

Rebooting the application (my CF app… not the server) does nothing. Clearing the query cache does nothing. Changing positions at my desk for new perspectives does nothing. So far, the server admins and I found only 2 things that will fix the problem. 1) Time. (time heals many things) and 2) In CF Admin, disable/re-enable the database connection (Datasource … Disable connections checkbox)

Item number 2 above does the trick, but I gotta believe there is a better way. I hoping one of the three people that read my blog might have a suggestion. Anybody?