Problem with Passing Integers to Oracle StoredProcedures

[stlaw]

I've been working with Oracle Stored procedures for a short time and have run
into a glitch
when working with data types of integer. I have a form that I use to collect
data from users
to add/edit records in an Oracle table. The form has a text field that
validates the data as integer.
When the form is submitted, I use a cfstoredprocedure call with cfparam type
cf_sql_integer to
pass the integer value to the Oracle stored procedure. If the number entered
into the text field
on the form is strictly numeric, ie. 1200 the data is okay. If the user
enters a number with a
comma in it, ie. 1,200 (which will not raise a validation error on type
integer) the number
that ends up in the Oracle field is incorrect. It usually ends up being a
large negative number.

[paross1]

How are you validating that your input is numeric? If I use the IsNumeric()
function, I get a NO for something like IsNumeric("1,234") but a YES for
IsNumeric("1234"). Are you using CFFORM? You will probably need to either
perform further tests on the input and reject values that contain non-numeric
characters, or remove them yourself by parsing the string and removing anything
that isn't a number.

Phil

[stlaw]

Phil,
Thanks for your reply. I'm using a cfform with a cfinput type="text,
validate="integer", validateat="onblur, onserver". Validation as a whole
doesn't appear to be working well for me. On some cfinputs it doesn't perform
the validation at all, even though I copied and pasted the validate="integer"
and validateat="onblur" from a field where it does work. For the fields that
it does work on, it appears that the non-numeric characters of plus, minus, and
comma will pass validation for integer. You can put any of these characters
anywhere (12,+34,56) and it will pass validation. If you put a decimal or any
other character or symbol in the input, it will fail validation. This must be
a bug. Unfortunately the lesson learned is that I can't rely on CFForm
validation. :brokenheart;

[paross1]

Since you are using stored procedures, you could even do the validation in the
PL/SQL itself. You would need to change your parameters to VARCHAR2 instead of
NUMBER, and probably have an OUT parameter as a procedure status, so that if
the "integer" parameter that was passed to the procedure ended up being
non-numeric, your ColdFusion code could evaluate the status parameter and act
accordingly. In other words, you do have options. One thing about making all of
your stored procedure parameters VARCHAR2 is that you avoid type mismatch
errors, and you can control how you want to handle bad data within your
procedure, which can be a good idea, especially if you plan on calling these
procedures from other front ends, etc. (For example, I pass all of my date
values as VARCHAR2, which has saved me tons of problems as we have migrated
through various versions of ColdFusion and Oracle. I do the conversions in the
procedure, if necessary, and handle any exceptions accordingly.)

Just something to think about.

Phil

[stlaw]

Phil,

I was actually planning on doing the validation on the Oracle side and using
replace to strip the commas out, but I've found an even better solution. If I
change my cfparam to cf_sql_varchar instead of cf_sql_integer and in the oracle
stored procedure map the data type to the field in the oracle table instead of
using type number, it works like a charm. I've used data type mapping for
dates which works really well, but I hadn't thought about it for numbers. To
map a data type in an Oracle stored procedure you use the following coding
parameter1 in tablename.fieldname%type

Thanks again for your suggestions.

Theresa

[paross1]

I almost always use type mapping, as it saves you a LOT of work if you ever have to alter a column in the database, etc., especially for local variables in your procedure.

Phil