CLOB Update Failing On Content Below Character Limit

I am trying to update a CLOB field using form input in ColdFusion 8. The data being submitted is no more than 10KB in size, yet I get the ORA-01704: string literal too long error.

<cfquery name="updatePubs" datasource="#authConfig.getConfigSetting('datasource')#">
  UPDATE members
  SET publication = '#publications#'
  WHERE campus_key = #id#
</cfquery>

Is there an alternate way to do this? It updates fine for data just below 4KB, but above that it throws the error.

Solution

Try wrapping the variables within your query with <cfqueryparam> tags. Like this:

<cfquery name="updatePubs" datasource="#authConfig.getConfigSetting('datasource')#">
  UPDATE members
  SET publication = <cfqueryparam cfsqltype="CF_SQL_CLOB" value="#publications#">
  WHERE campus_key = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#id#">
</cfquery>

Note that I am assuming your id is of type integer in my example.

The <cfqueryparam> tag does the following:

Allows the use of SQL bind parameters, which improves performance.
Ensures that variable data matches the specified SQL type.
Allows long text fields to be updated from a SQL statement.
Escapes single-quotation marks in string variables.

To benefit from the enhanced performance of bind variables, use cfqueryparam for all ColdFusion variables, and your DBMS must support bind variables. If a DBMS does not support bind parameters, ColdFusion validates and substitutes the validated parameter value back into the string. If validation fails, it returns an error message.

Here is a link to the documentation for cfqueryparam