Oracle, CFQueryParam and Decimal Places

Though I have worked with ColdFusion for about 14 years now, I only recently encountered an issue where a ColdFusion query did not match correctly on a value with 3 decimal places. Moreover, the issue originated in our QA sandbox but not in the development sandbox.

So there were a few things going on here. First, my development sandbox was using the Oracle JDBC thin client, while the QA environment was using the included Oracle "thick" client. Second, the query worked perfectly in my sandbox and in Oracle SQL Developer.

In the query itself I was using a CFQUERYPARAM, as everyone should. (You are using CFQUERYPARAM, all the time, right?) I had the CFSQLTYPE set to "CF_SQL_NUMERIC." I tried switching to "CF_SQL_DECIMAL" but still no joy. Then it dawned on me, I had not specified a scale; that is how many decimal places this field has, and guess what? The default scale is zero, and thus the built in Oracle Client was truncating the decimal places!

This field has at most three decimal places, and the fix was to simply add the SCALE attribute of 3 to the CFQUERYPARAM for the field. Now my query is all happy, happy, joy, joy on both boxes.

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.