CFSQLTYPE="VARCHAR2" ???

The datatype VARCHAR2 is an Oracle-specific datatype, I think. It's not in the list of supported parameter types that CF lists. But I'm not sure that really matters. My guess is that CF normally converts entries like "cf_sql_varchar" to whatever the database-specific equivalent happens to be. In the absence of being able to do that conversion, if it just gets passed through as-is, VARCHAR2 will work fine with Oracle since it's an Oracle datatype. I'm honestly not much of an Oracle guy, so I don't know what happens specifically with Oracle, but I wouldn't be surprised if it was specified intentionally because Oracle differentiates between VARCHAR2 and VARCHAR, and the programmer wanted to force the use of VARCHAR2 instead of VARCHAR. (The differentiation is really pretty minor, though.)

I don't know why CF doesn't throw an error with that value, but I can only assume that CF will let you pass through anything as long as the database itself will accept it. If you enter a CFSQLTYPE value that is neither supported by CF nor your database, your database would throw an error and the query wouldn't execute, so you wouldn't get very far. This whole CFQUERY and CFQUERYPARAM set is used to build a parameterized query in your database, and if the database doesn't support the parameters you're using, you'll know it. If you entered the wrong CFSQLTYPE datatype, for example a cf_sql_integer where you really wanted to use cf_sql_varchar, that'll cause a database error as well.

If you're really interested in seeing this at work, you should be able to view executed queries in Oracle and see what they look like. I don't know how to do that offhand, but I know that it can be done in SQL Server and other databases so I'm sure it can be done in Oracle also.

Dave Watts, CTO, Fig Leaf Software

I think it checks the type you have put in and then passes the type through. If no type is supplied or the type is not a correct one, it just defaults to a string check (i.e. varchar) - in this case it would work as that is what the field is.

The documentation tells you the order of the validation -

For these types, a data value can be converted to a numeric value: CF_SQL_SMALLINT, CF_SQL_INTEGER, CF_SQL_REAL, CF_SQL_FLOAT, CF_SQL_DOUBLE, CF_SQL_TINYINT, CF_SQL_MONEY, CF_SQL_MONEY4, CF_SQL_DECIMAL, CF_SQL_NUMERIC, and CF_SQL_BIGINT For these types, a data value can be converted to a date supported by the target data source: CF_SQL_DATE, CF_SQL_TIME, CF_SQL_TIMESTAMP For all other types, if the maxLength attribute is used, a data value cannot exceed the maximum length specified. ColdFusion debug output shows the bind variables as question marks and lists the values beneath the query, in order of usage.

The code you used is probably the equivalent of excluding the cfsqltype attribute. Although it works, its technically incorrect as its an invalid type