Outputting Coldfusion Parameters in cfstoredproc stored procedures

Last week I was running into an issue with a stored procedure. Passing parameters to the procedure using cfstoredproc and cfprocparam.

Here is how I solved it to trap the problem which was a HY000 error returned by Oracle.

using a combination of cfoutput and pre and then clipping the < part of the container tags and then closing the cfoutput and pre tags I was able to determine a few things.


< cfoutput >
< pre >
CFstoredproc procedure="schema.package.procedure" datasource="dsn"> cfprocparam type="inout" cfsqltype="CF_SQL_Number" dbvarname="mID" value="#trim(variables.mID)#" null="#evaluate("variables.mID is """"")#"> cfprocparam type="in" cfsqltype="CF_SQL_Varchar" dbvarname="pName" value="#form.pname#"> cfprocparam type="in" cfsqltype="CF_SQL_Number" dbvarname="pMedium" value="#trim(form.pMedium)#"> CFStoredproc>
<  /pre >
< /cfoutput >
< cfabort >

(where you see the actual container tags spaced out is because of blogger doing some clean-up...I forgot how frustrating this can be :) )

I re-saved the code snippet and visited the page in my browser.  I was able to determine that I actually had more than one problem.  I was passing part of a form tag to my procedure parameter which was a coding error on my part, and also one of my variable data types were mismatching.  For example a string where a number was expected by the procedure.  I got my data types fixed removed the pre tag and the cfoutput surrounding it and it worked like a charm.  i hope this helps others who have got stuck on something like this.